Page 1 of 2

Need help on query error!

Posted: Mon Mar 12, 2007 4:07 pm
by highjo
Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


hello! guys ,i'm new in php and using a SAMS "php and MySql web development" ebook and i'm on the stage in which we retreive info from the database through html search page which is below:

[syntax="html"]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Book-o-Rama Catalogue search</title>
</head>

<body>
<h1>Book-o-Rama Catalogue search</h1>
<form action="result.php" method="post">
Choose search Type:<br />
<select name="searchtype">
<option value="author">Author
<option value="title">Title
<option value="isbn">ISBN
</select>
<br />
Enter search Term:<br />
<input name="searchterm" type="text" />
<br />
<input type="submit" value="search" />
</form>
</body>
</html>
and the php script that is called on submit is result.php that is bellow:[/syntax]

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Book-o-Rama search results</title>
</head>

<body>
<h1>Book-o-Rama search results</h1>
<?php 
$searchtype = $_POST["searchtype"];
$searchterm = $_POST["searchterm"];
trim($searchterm);
if (!$searchtype || !$searchterm)
	{
		echo "you have not entered searchdetails.Please go back and try again.";
		exit;
	}
	
	$searchtype = addslashes($searchtype);
	$searchterm = addslashes($searchterm);
	
	 $db = mysql_pconnect("localhost","root","mamamia");
	
	if (! $db)
		{
			echo "Error: could not connect to the Database.Please try again later.";
			exit;
		}
	
	mysql_select_db("books");
	$query = "select*from books where".$searchtype."like'%".$searchterm."%'";
	$result = mysql_query($query);
	$num_results = mysql_num_rows($result);
	
	echo "<p> Number of books found: ".$num_results."</p>";
	
	for($i = 0; $i<$num_results ;$i++)
		{
			$row = mysql_fetch_array($result);
			echo "<p><strong>".($i+1).".Title : ";
			echo htmlspecialchars(stripslashes($row["title"]));
			echo "</strong><br>Author : ";
			echo htmlspecialchars(stripslashes($row["author"]));
			echo "<br>ISBN: "; 			
			echo htmlspecialchars(stripslashes($row["isbn"]));
			echo "<br>Price : ";
			echo htmlspecialchars(stripslashes($row["price"]));
			echo "</p>" ;

		}
	
?>
</body>

</html>
I'm using dreamweaver 8 with xampp v1.5.5 and the fact is that the php code is having a error that i can't find 2 weeks now.I don't anderstand.every thing is working apart from this one.
I need serious help.


Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon Mar 12, 2007 4:12 pm
by louie35
i take it searchtype is the field in the database so here it goes:

Code: Select all

$query = "select * from books where ".$searchtype." like '%".$searchterm."%' ";
not the spaces

Posted: Mon Mar 12, 2007 4:20 pm
by highjo
please can you be more precise?

Posted: Mon Mar 12, 2007 4:52 pm
by RobertGonzalez
That is the crappiest tutorial I have ever seen. My goodness, are they trying to teach you to be a bad programmer?

Change this:

Code: Select all

<?php
$query = "select*from books where".$searchtype."like'%".$searchterm."%'"; 
?>
to

Code: Select all

<?php
$query = "select * from books where $searchtype like '%$searchterm%'"; 
?>
Then try it. If it works, go out and get a good book instead of that garbage.

Posted: Tue Mar 13, 2007 3:31 am
by highjo
thanks i'll try it.What ebook do you think i should use for learning php the good way and where to find it?
One thing i like in this forums is that there is alays somebody to answer.i really like it.thanks a lot

Posted: Tue Mar 13, 2007 10:37 am
by RobertGonzalez
Honestly, I would search these forums for 'tutorials' or 'php books'. There are a lot of them out there. And all should be teaching better than what you are learning from.

Posted: Tue Mar 13, 2007 2:53 pm
by highjo
i tried it and it's not working,someting wrong with mysql_num_rows()

Posted: Tue Mar 13, 2007 3:43 pm
by RobertGonzalez
Post your new code. I'd bet there is something wrong with the query, not mysql_num_rows().

Posted: Mon Mar 19, 2007 2:37 pm
by highjo
i've pick a copy of An O'reilly chapter on "php talk to mySql" and it teaches pretty well how to connect to my sql,so i did some tweaks on my codes:

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>search result</title>
</head>

<body>
<?php 
include ('db_info.php');
$searchtype = $_POST["searchtype"];
$searchterm = $_POST["searchterm"];
//$typename= addslashes($typename);

$db = mysql_connect($db_host, $db_username, $db_password);

if(!$db)
{
die ("cannot connect to the database:  <br />".mysql_error());
}

$db_select = mysql_select_db($db_database);
if (!$db_select){
die ("cannot connect to the select database:  <br />".mysql_error());
}
$select = ' select ';
$column = ' * ';
$from = ' from ';
$table = ' books ';
$where = ' where ';
$like = ' like ';
/*$query = $select.$column.$from.$table.$where.$searchtype.$like.$searchterm;*/
$query = "select * from books where $searchtype 'like' $searchterm ";
$result = mysql_query( $query );
if (!$result){
die ("could not query the database:  <br />".mysql_error());
}
$num_results = mysql_num_rows( $result );
echo "<p> Number of books found: ".$num_results."<p>";
for ($i = 0; $i < $num_results; $i++)
{
 			$row = mysql_fetch_array($result);
			echo "<p><strong>".($i+1).".Title : ";
			echo $row["title"];
			echo "</strong><br>Author : ";
			echo $row["author"];
			echo "<br>ISBN: "; 			
			echo $row["isbn"];
			echo "<br>Price : ";
			echo $row["price"];
			echo "</p>" ;
}

mysql_close($db);

?>
</body>
</html>

this alone run whithout a problem but called by the mysearch.html search page there is a syntax error about the key word like.
if i do this:

Code: Select all

$query = "select * from books where '$searchtype' like '$searchterm' ";
from the search page it is not considering the $searchtype and $searchterm and it is showing every thing on the database table "books".don't know what is going on :oops:

Posted: Mon Mar 19, 2007 2:46 pm
by highjo
forgot to add the error text on my page that i was talking about:

Code: Select all

could not query the database: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''like' Michael Morgan' at line 1

Posted: Mon Mar 19, 2007 4:14 pm
by RobertGonzalez
Is $searchtype your field name? If so, take the single quotes off of it.

Posted: Tue Mar 20, 2007 6:49 am
by highjo
$searchtype and $searchterm are field names.When i took Single quotes off like this

Code: Select all

$query = "select * from books where $searchtype like $searchterm ";
i see this error

Code: Select all

could not query the database: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near like at line 1
but if i put like keyword in quote like this

Code: Select all

$query = "select * from books where $searchtype 'like' $searchterm ";
i see this

Code: Select all

could not query the database: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''like' Michael Morgan' at line 1
because $searchterm store "Michael Morgan" from the search page and $searchtype stor the name of a column in my database's books table.It's seems like $searchtype variabl e srting is empty.I added this

Code: Select all

echo $searchtype ;
 echo $searchterm;
to myresult.php and i see them: author and Michael Morgan
what to do??

Posted: Tue Mar 20, 2007 8:29 am
by feyd
  • Add code to ensure $searchtype is an acceptable field.
  • $searchterm will require quotes around it as well as escaping (at minimum) in the query string.

Posted: Tue Mar 20, 2007 10:49 am
by RobertGonzalez

Code: Select all

<?php
$query = "select * from `books` where `$searchtype` like '%$searchterm%'";
?>

Posted: Wed Mar 21, 2007 4:11 pm
by highjo
Hey! thanks a lot :lol: that worked.very gratefull tha you spent time to read my post and gave me some clue.
i have a another question :oops: why
`$searchtype` and not `$searchterm`?