Page 1 of 1

PHP code error...maybe?

Posted: Sun Jun 01, 2008 1:21 pm
by lostprophetpunk
Right, I have my MySQL database set up. It has two entries in it.

Two of the field entries are exactly the same.

I have a page called 'search.php' which has several forms.

Now the first two forms on that page are text entry. Now, when I type the correct spelling of either of the entries it doesn't show anything. But when I type in another of the text input with the Author the same as the two entries, only one of them shows, and the first one doesn't show. The code for search.php is shown below...

Code: Select all

 
<html>
<head>
<body>
 
<div id='searchbox'>
<br /><h1>Book Search</h1><br />
<form action="books.php" method='POST'>
Book Title:&nbsp;&nbsp;
<input type="text" name="books">
<INPUT type="submit" name="go" value="Search!">
</form>
 
<br /><br />
 
<form action="books.php" method='POST'>
Author:&nbsp;&nbsp;
<input type="text" name="author">
<INPUT type="submit" name="go" value="Search!">
</form>
 
<br /><br />
 
<form action="books.php" method='POST'>
Price:&nbsp;&nbsp;
<select name="price">
<option value="nothing"></option>
<option value="£1.99">£1.99</option>
<option value="£2.99">£2.99</option>
<option value="£3.99">£3.99</option>
<option value="£4.99">£4.99</option>
<option value="£5.99">£5.99</option>
<option value="£6.99">£6.99</option>
<option value="£7.99">£7.99</option>
<option value="£8.99">£8.99</option>
<option value="£9.99">£9.99</option>
<option value="£10.99">£10.99</option>
</select>
<INPUT type="submit" name="go" value="Search!">
</form>
 
<br /><br />
 
<form action="books.php" method='POST'>
Publication Year:&nbsp;&nbsp;
<select name="year">
<option value="nothing"></option>
<option value="1810">1810</option>
<option value="1820">1820</option>
<option value="1830">1830</option>
<option value="1840">1840</option>
<option value="1850">1850</option>
<option value="1860">1860</option>
<option value="1870">1870</option>
<option value="1880">1880</option>
<option value="1890">1890</option>
<option value="1900">1900</option>
<option value="1940">1940</option>
</select>
<INPUT type="submit" name="go" value="Search!">
</form>
 
<br /><br />
 
<form action="books.php" method='POST'>
Country of Origin:&nbsp;&nbsp;
<select name="origin">
<option value="nothing"></option>
<option value="UK">UK</option>
<option value="America">America</option>
<option value="Australia">Australia</option>
<option value="Germany">Germany</option>
<option value="France">France</option>
<option value="Spain">Spain</option>
<option value="Russia">Russia</option>
<option value="Argentina">Argentina</option>
<option value="Denmark">Denmark</option>
<option value="Fiji">Fiji</option>
</select>
<INPUT type="submit" name="go" value="Search!">
</form>
 
<br /><br />
 
<form action="books.php" method='POST'>
Condition:&nbsp;&nbsp;
<select name="condition">
<option value="nothing"></option>
<option value="used">Used</option>
<option value="new">New</option>
</select>
<INPUT type="submit" name="go" value="Search!">
</form>
 
</div>
 
<?php
$book=$_POST['books'];
$author=$_POST['author'];
$price=$_POST['price'];
$year=$_POST['year'];
$origin=$_POST['origin'];
$condition=$_POST['condition'];
?>
</body>
</html>
 
Now, that code above should store all of the values into variables so that they can be used with 'books.php'.

The code shown below is 'books.php'...

Code: Select all

 
<?php
$result = mysql_query("SELECT * FROM books WHERE Book_Title='$books' or Author='$author' or Price='$price' or Publication_Year='$year' or Origin_Country='$origin' or Condition='$condition'") 
or die(mysql_error());  
 
$row = mysql_fetch_array( $result );
 
echo "<table border='0' align='center' style='background-color: #153E7E;border: 1px solid #ffffff;color: #ffffff;font-family: verdana, sans-serif;font-size: 14px;font-weight: 900;'>";
echo "<tr> <th class='styled' style='border-left:none;'>Book Title</th> <th class='styled'>Author</th> <th class='styled'>Price</th> <th class='styled'>Year of Publication</th> <th class='styled'>Country of Origin</th> <th class='styled'>Condition</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row
    echo "<tr><td class='styles' style='border-left: none;'>"; 
    echo $row['Book_Title'];
    echo "</td><td class='styles'>";
        echo $row['Author'];
    echo "</td><td class='styles'>";
        echo $row['Price'];
    echo "</td><td class='styles'>";
        echo $row['Publication_Year'];
    echo "</td><td class='styles'>";
        echo $row['Origin_Country'];
    echo "</td><td class='styles'>";
        echo $row['Condition'];
    echo "</td></tr>";
}
 
echo "</table>";
 
?>
 
Note that I have left the connection code out.

Visit the search page here. You can then type in 'Dan Brown' for the author search field, and then click on search. This will display one field.

However, there are two rows of data in the table, and they both have 'Dan Brown' as the author. Also, when typing either of the book titles into the appropriate field nothing shows up.

I would be very greatful is someone could help me out with my problem.

Re: PHP code error...maybe?

Posted: Sun Jun 01, 2008 3:50 pm
by califdon
You seem to have your two scripts a little mixed up. The search.php shouldn't have the lines at the bottom, creating variables from the $_POST array, because there is no data being sent to that script, as far as I can see. Those lines should be at the top of your books.php script. I don't see how your code ever works, since you didn't show where the variables like $books, $author, etc. are given any values. To put it differently, $_POST is an array that is formed and available to a script when another script (or it could be the same one, if you include conditionals to determine which part of the code you want to execute) calls that script in the action= parameter of a <form> element. So it can only be read by the action script, not by the script that calls it.

Re: PHP code error...maybe?

Posted: Mon Jun 02, 2008 2:00 am
by lostprophetpunk
I have tried what you said. But it still is doing the same thing. It only shows one row when I type the author in which is the same for both rows of data, and I still get nothing out of the title search.

Re: PHP code error...maybe?

Posted: Mon Jun 02, 2008 12:23 pm
by califdon
lostprophetpunk wrote:I have tried what you said. But it still is doing the same thing. It only shows one row when I type the author in which is the same for both rows of data, and I still get nothing out of the title search.
OK, so now you have your $_POST lines in the second script, right? The next thing that I notice is that your Select elements all have a beginning option of "nothing". You probably don't realize it, but that means that they will create values of the string "nothing" in the $_POST array, and your script will be searching for the string "nothing" in your records if the user doesn't select something else. I would remove all those options. Thirdly, you have written your script in such a way that it makes it difficult to do any debugging. Instead of:

Code: Select all

$result = mysql_query("SELECT * FROM books WHERE Book_Title='$books' or Author='$author' or Price='$price' or Publication_Year='$year' or Origin_Country='$origin' or Condition='$condition'")
 or die(mysql_error());
do this:

Code: Select all

$sql = "SELECT * FROM books ";
   $sql .= "WHERE Book_Title='$books' OR Author='$author' OR Price'$price' ";
   $sql .= "OR Publication_Year='$year' OR Origin_Country='$origin' ";
   $sql .- "OR Condition='$condition' ";
$result = mysql_query($sql) or die(mysql_error());
Now you can add a temporary debugging line like this, just before the $result = line, to show you what you are actually sending to MySQL. Often this will reveal quite unexpected results, leading directly to solving the problem.

Code: Select all

 echo "SQL = $sql";
Finally, I'm not really sure how PHP will handle this, you may be okay, but it's unusual to have multiple <form> elements on the same page, without names for each form. I think I would have only one <form> and <submit>. The added advantage would be that the user could enter or choose more than one element to search on, since your query is set up to do that, anyway (assuming that you remove those "nothing" options).

Re: PHP code error...maybe?

Posted: Mon Jun 02, 2008 1:02 pm
by lostprophetpunk
I have now got an error after changing the relevant parts...
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 ''' OR Publication_Year='' OR Origin_Country=''' at line 1

Re: PHP code error...maybe?

Posted: Mon Jun 02, 2008 1:22 pm
by califdon
califdon wrote:do this:

Code: Select all

$sql = "SELECT * FROM books ";
   $sql .= "WHERE Book_Title='$books' OR Author='$author' OR Price'$price' ";
   $sql .= "OR Publication_Year='$year' OR Origin_Country='$origin' ";
   $sql .- "OR Condition='$condition' ";
$result = mysql_query($sql) or die(mysql_error());
Now you can add a temporary debugging line like this, just before the $result = line, to show you what you are actually sending to MySQL. Often this will reveal quite unexpected results, leading directly to solving the problem.

Code: Select all

 echo "SQL = $sql";
So, did you do that? What was echoed to the screen?

Re: PHP code error...maybe?

Posted: Mon Jun 02, 2008 1:53 pm
by lostprophetpunk
The following was posted before the error...
SQL = SELECT * FROM books WHERE Book_Title='Angels & Demons' OR Author='' OR Price'£1.99' OR Publication_Year='1810' OR Origin_Country='UK'

Re: PHP code error...maybe?

Posted: Mon Jun 02, 2008 2:12 pm
by califdon
lostprophetpunk wrote:The following was posted before the error...
SQL = SELECT * FROM books WHERE Book_Title='Angels & Demons' OR Author='' OR Price'£1.99' OR Publication_Year='1810' OR Origin_Country='UK'
Aha! So now do you see what's wrong? What do you suppose is missing between "Price" and " '£1.99' "? :)

Re: PHP code error...maybe?

Posted: Tue Jun 03, 2008 1:40 am
by lostprophetpunk
Right, I have fixed that. But when I type in the book title it doesn't show it. Also, whenever I still type in the same author for both of the MySQL data rows it only displays one row.

Re: PHP code error...maybe?

Posted: Tue Jun 03, 2008 5:25 pm
by califdon
lostprophetpunk wrote:Right, I have fixed that. But when I type in the book title it doesn't show it. Also, whenever I still type in the same author for both of the MySQL data rows it only displays one row.
What I had hoped I was showing you was how to go about debugging your script. I can't find every error that might exist in your script, nor can I guess whether your data may have a leading space or some other cause for the results that you get. If you apply the same technique of echoing the value of your SQL queries, and study what they are doing, you should be able to see why it is giving those results. That will be of far greater value to you than just having someone do all the work for you.