If I tried this`
function get_books_sql ( )
{
global $CRUD;
$dbh = $CRUD['dbh'];
$title = '';
$fullName = '';
$name = '';
$Ktitle = '';
$Psurname = '';
if(isset($_POST['Ktitle'])){
$title = $_POST['Ktitle'];
}
if(isset($_POST['Surname'])){
$fullName = $_POST['Psurname'];
$name = explode(" ", $fullName);
}
$sth = $dbh->prepare(" SELECT * FROM book JOIN stock ON stock.k_id = book.k_id WHERE (book.title LIKE :Ktitle ) AND stock.sold = FALSE ");
$sth->bindValue(':Ktitle', $title, PDO::PARAM_STR);
if($sth) $sth->execute(array('%'.$title.'%'));
else error('get_books_sql: select prepare returned no statement handle');
$err = $sth->errorInfo();
if($err[0] != 0) error( $err[2] );
return($sth);
}
` it works when I insert keyword for Book title and if I want to do both for book title keyword andfor author name and surname I want to filtered with keyword title and keywords of name and surname of author, both queries alone work but I have problem with Author name and surname and I dont know how can I bind them and execute
There is main.php file:
<!-- main html file for CRUD (php version) -->
<?php echo $CRUD["MESSAGES"] ?><?php echo $CRUD["ERRORS"] ?>
<div class="form">
<form action="<?php echo $CRUD["SELF"] ?>" method="post" name="book">
<p class="subheading"><?php echo $CRUD["FORM_HEAD"] ?></p>
<table class="form">
<tr>
<td><p class="Afield"> Title:</p></td>
<td><input class="Afield" type="text" name="Ktitle" value="<?php echo $CRUD["Ktitle"] ?>"> </td>
</tr>
<tr>
<td><p class="Afield"> Isbn:</p></td>
<td><input class="Afield" type="text" name="kisbn" value="<?php echo $CRUD["Kisbn"] ?>"> </td>
</tr>
<tr>
<td><p class="Afield"> Cena:</p></td>
<td><input class="Afield" type="text" name="Kprice" value="<?php echo $CRUD["Kprice"] ?>"> </td>
</tr>
<tr>
<td><p class="Afield"> Author:</p></td>
<td><input class="Afield" type="text" name="Psurname" value="<?php echo $CRUD["Pname"] ?><?php echo $CRUD["Psurname"] ?>"> </td>
</tr>
<tr class="buttons"><td colspan="2">
<p class="buttons">
<?php echo $CRUD["BUTTONS"] ?><?php echo $CRUD["HIDDENS"] ?>
</p>
</td></tr>
</table>
</form>
</div>
<?php echo $CRUD["PRECONTENT"] ?><?php echo $CRUD["CONTENT"] ?><?php echo $CRUD["POSTCONTENT"] ?>
This I did for onyl Author: `
function get_books_sql ( )
{
global $CRUD;
$dbh = $CRUD['dbh'];
$title = '';
$fullName = '';
$name = '';
$Ktitle = '';
$Psurname = '';
if(isset($_POST['Ktitle'])){
$title = $_POST['Ktitle'];
}
if(isset($_POST['Surname'])){
$fullName = $_POST['Psurname'];
$name = explode(" ", $fullName);
}
$sth = $dbh->prepare("SELECT * FROM book, author, stock WHERE (CONCAT( author.name , ' ', author.surname ) ILIKE :Psurname ) AND book.p_id = author.p_id AND book.k_id = stock.k_id AND stock.sold = false ");
$sth->bindValue(':Psurname', $fullName, PDO::PARAM_STR);
if($sth)
$sth->execute();
else error('get_knjige_sql: select prepare returned no statement handle');
$err = $sth->errorInfo();
if($err[0] != 0) error( $err[2] );
return($sth);
}`
This is my whole query which works with tested datas: SELECT * FROM book, author, stock WHERE (book.title ILIKE '%a%' AND CONCAT ( author.name, ' ', author.surname ) ILIKE '%pavcek%' ) AND book.p_id = author.p_id AND book.k_id = stock.k_id AND stock.sold = false;
but I have to do so I mean: SELECT * FROM book, author, stock WHERE (book.title ILIKE :Ktitle AND CONCAT ( author.name, ' ', author.surname ) ILIKE :Psurname ) AND book.p_id = author.p_id AND book.k_id = stock.k_id AND stock.sold = false;
%
wildcards for the ILIKE matching? – Richard Huxton Dec 17 '13 at 13:22