Page 1 of 2

Help with php/MySql query

Posted: Fri Oct 21, 2005 1:00 pm
by colorproof
I'm fairly new to the world of PHP and have been working with the book, "PHP & MySQL Web Development" by Luke Welling and Laura Thomson and so far, it is a great book. The script that I am having problems with comes directly from that book so for any of you that may have that book, the chapters I am dealing with are chapter 10 - 11.

My site is being hosted and the version of PHP installed on the server is 4.3

So basically, I created a MySQL database named "books" and inserted the required tables and fields. This database "books" is a catalog of books (title, ISBN) and customer information.

The page "search.html" is a simple form which allows a query to be performed. You can search the ISBN, Author, or book title. The html code is below:

Code: Select all

<html>
<head>
  <title>Book-O-Rama Catalog Search</title>
</head>

<body>
  <h1>Book-O-Rama Catalog Search</h1>

  <form action="results.php" method="post">
    Choose Search Type:<br />
    <select name="searchtype">
      <option value="author">Author</option>
      <option value="title">Title</option>
      <option value="isbn">ISBN</option>
    </select>
    <br />
    Enter Search Term:<br />
    <input name="searchterm" type="text">
    <br />
    <input type="submit" value="Search">
  </form>

</body>
</html>
Here is the PHP/MySQL code for "results.php" This is what has been giving me problems:

Code: Select all

<html>
<head>
  <title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>

<?php
  // create short variable names
  $searchtype=$_POST['searchtype'];
  $searchterm=$_POST['searchterm'];

  $searchterm= trim($searchterm);

  if (!$searchtype || !$searchterm)
  {
     echo 'You have not entered search details.  Please go back and try again.';
     exit;
  }
  
  if (!get_magic_quotes_gpc())
  {
    $searchtype = addslashes($searchtype);
    $searchterm = addslashes($searchterm);
  }

  @ $db = mysqli_connect('localhost', 'username', 'password', 'domain_com_-_books');

  if (mysqli_connect_errno()) 
  {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }

  $query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = mysqli_query($db, $query);

  $num_results = $result->num_rows;

  echo '<p>Number of books found: '.$num_results.'</p>';

  for ($i=0; $i <$num_results; $i++)
  {
     $row = mysqli_fetch_assoc($result);
     echo '<p><strong>'.($i+1).'. Title: ';
     echo htmlspecialchars(stripslashes($row['title']));
     echo '</strong><br />Author: ';
     echo stripslashes($row['author']);
     echo '<br />ISBN: ';
     echo stripslashes($row['isbn']);
     echo '<br />Price: ';
     echo stripslashes($row['price']);
     echo '</p>';
  }
  
  $row = mysqli_fetch_object($result);
  $db->close();

?>
</body>
</html>
So what happens when I search is that the "results.php" comes back with absolutely nothing, not even an error message. When I change the name of the database so that the script will not be able to connect, it still does not give me a connection error. I find that to be very odd. I know that this script uses the mysqli library which is object-oriented, and PHP version 4 and above should support that so I don't see why that could be a problem. I substituted procedural syntaxes for the mysqli to see if it would work and it still did the same thing--returned a page with absolutely nothing on it.

Does anybody know what's going on? It would help me so much to figure this out!! Thanks a lot.

Posted: Fri Oct 21, 2005 1:37 pm
by RobertGonzalez
Have you tried to view source of the resulting HTML? Also, have you tried throwing echo statements inside and outside of the script? That may tell you if you are having problems. Another thing to do, and I am finding this to be a very useful exercise, is to clear your cache between changes. Sometimes the changes take place but your page loads from cache and shows no change.

One more thing. Try leaving a field blank and see if the error message pops up.

Hope this helps.

Posted: Fri Oct 21, 2005 1:48 pm
by colorproof
Wow, thanks for the prompt reply! Yes, I have viewed the source of the resulting HTML. When I say that nothing shows up, I forgot to mention that anything outside of the php script shows up just fine. I've cleared the cache yet that did not resolve the situation.

Although, when I do leave the search field empty, the error shows up. Only when I have something typed in the field, an empty results page appears.

Posted: Fri Oct 21, 2005 1:50 pm
by Charles256
just your tags around your php from code to php tags and I'll take a look at the source... :-D

Posted: Fri Oct 21, 2005 4:46 pm
by RobertGonzalez
colorproof wrote:Wow, thanks for the prompt reply! Yes, I have viewed the source of the resulting HTML. When I say that nothing shows up, I forgot to mention that anything outside of the php script shows up just fine. I've cleared the cache yet that did not resolve the situation.

Although, when I do leave the search field empty, the error shows up. Only when I have something typed in the field, an empty results page appears.
OK, it sounds like mysqli might not be supported properly in your PHP version. Start by changing this line...

Code: Select all

@ $db = mysqli_connect('localhost', 'username', 'password', 'domain_com_-_books');
to this line...

Code: Select all

$db = @mysqli_connect('localhost', 'username', 'password', 'domain_com_-_books');
Then add an else to the error trapper to see what comes out...

Code: Select all

if (mysqli_connect_errno())
  {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  } 
  else
  {
    echo 'We are connected to the database';
  }
Sorry for the delay, I was away for the last 40 minutes handling an emergency.

The last thing to do is fix this...

Code: Select all

for ($i=0; $i <$num_results; $i++)
  {
     $row = mysqli_fetch_assoc($result);
     echo '<p><strong>'.($i+1).'. Title: ';
     echo htmlspecialchars(stripslashes($row['title']));
     echo '</strong><br />Author: ';
     echo stripslashes($row['author']);
     echo '<br />ISBN: ';
     echo stripslashes($row['isbn']);
     echo '<br />Price: ';
     echo stripslashes($row['price']);
     echo '</p>';
  }
Maybe change it to a while loop with a counter to get $i...

Code: Select all

$counter = 0;
while ($row = mysqli_fetch_assoc($result))
{
     echo '<p><strong>'.($counter + 1).'. Title: ';
     echo htmlspecialchars(stripslashes($row['title']));
     echo '</strong><br />Author: ';
     echo stripslashes($row['author']);
     echo '<br />ISBN: ';
     echo stripslashes($row['isbn']);
     echo '<br />Price: ';
     echo stripslashes($row['price']);
     echo '</p>';
     $counter++;
}
See what that gives you then post back.

Posted: Fri Oct 21, 2005 5:31 pm
by colorproof
First of all, I appreciate your help SO much! Unfortunately, the code you provided me gave me the same results as before.

I just find it weird that there is no error! Even when I purposely mistype the username or password, it gives no error.

I contacted my webhost and asked if they support mysqli, and they simply said that it should work fine, and if I have any questions to view the phpinfo().

Should I just find an alternative to the mysqli library altogether? I don't know enough about PHP and MySQL quite yet although the book I am using the learn requires mysqli for a majority of their tutorials.

Perhaps I should pick up a different book!

Posted: Fri Oct 21, 2005 5:42 pm
by Charles256
hum.try mysql instead of mysqli?:-D and i'm still waiting on you to edit your fist post and put it in php tags...

Posted: Fri Oct 21, 2005 5:50 pm
by colorproof
Hi Charles, sorry I didn't get back to you in the previous message, I must have misunderstood what you wrote!

What do you mean by posting it in php tags?

Also, I tried using mysql instead of mysqli and that didn't work out, it actually gave me a fatal error!

:)

Posted: Fri Oct 21, 2005 6:05 pm
by Charles256
grr.on your first post see where it says edit?click that and and see across from message body the tags that say B, i, u, quote, code, list, list=, img, URL, PHP!! hover over php and it tells you how to insert php tags, but the php tags around your php code. it makes a lot more readable:-D

and you wrote this..

Code: Select all

if (!$searchtype || !$searchterm)
try..

Code: Select all

if (empty($searchtype) || empty($searchterm))

Posted: Fri Oct 21, 2005 6:12 pm
by colorproof
Alright, I have changed the code to php, sorry about that!

:D

Posted: Fri Oct 21, 2005 6:20 pm
by Charles256
herm..try my above suggestion bout empty and if that don't work e-mail my all pertinent files to hyperiums@gmail.com (remove database user name and password pleasE) and i'll run it on my computer and see if i can get it running. :-D

Posted: Fri Oct 21, 2005 6:31 pm
by colorproof
I tried using empty to no avail. Sorry!

I e-mailed you the files necessary to get the script running. Thanks so much!!!

Posted: Fri Oct 21, 2005 7:03 pm
by Charles256
i don't suppose..

Code: Select all

$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = mysqli_query($db, $query);
should be..

Code: Select all

$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = mysqli_query($query,$db);
or maybe even just...
  $query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = mysqli_query($query);
or am i being silly? test it..

Posted: Fri Oct 21, 2005 7:05 pm
by RobertGonzalez
colorproof wrote:First of all, I appreciate your help SO much! Unfortunately, the code you provided me gave me the same results as before.
I just noticed that you never opened the mysqli Object (usually done along the lines of $mysqli = new mysqli(...);). In your result output you use the term

Code: Select all

$num_results = $result->num_rows;
$result in this case is used as an object of the mysqli class and num_rows is used as a property. If you haven't instantiated this object it might be the cause of your error.

Another thing to try is connecting using the mysql functions (like Charles256 said). You may also want to do everything procedurally instead of some procedural and some OOP. Maybe something along the lines of...

Code: Select all

<html>
<head>
  <title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>

<?php
  // create short variable names
  $searchtype=$_POST['searchtype'];
  $searchterm=trim($_POST['searchterm']);

  if (empty($searchtype) || empty($searchterm))
  {
     echo 'You have not entered search details.  Please go back and try again.';
     exit;
  }
  
  if (!get_magic_quotes_gpc())
  {
    $searchtype = addslashes($searchtype);
    $searchterm = addslashes($searchterm);
  }

  if(!$connect = mysql_connect('localhost', 'username', 'password')
  {
    die("Could not connect to the database server: " . mysql_error());
  }

  if (!$db = mysql_select_db('domain_com_-_books', $connect))
  {
    die("Cannot grab the database: " . mysql_error());
  }


  $query = "select * from books where $searchtype like '%$searchterm%'";
  if (!$result = mysql_query($query, $connect))
  {
    die("Could not get the requested info: " . mysql_error());
  }

  echo '<p>Number of books found: '.mysql_numrows($result).'</p>';

  $counter = 0;
  while($row = mysql_fetch_array($result))
  {
     echo '<p><strong>'.($counter + 1).'. Title: ';
     echo htmlspecialchars(stripslashes($row['title']));
     echo '</strong><br />Author: ';
     echo stripslashes($row['author']);
     echo '<br />ISBN: ';
     echo stripslashes($row['isbn']);
     echo '<br />Price: ';
     echo stripslashes($row['price']);
     echo '</p>';
     $counter++;

  }
  
  mysql_close();

?>
</body>
</html>
See if that gives you anything. Ideally, for error checking, you might want to throw echos into your output to see what is giving output and what is not. For something this small, it won't hurt to do that.

Let us know what you got.

Posted: Fri Oct 21, 2005 7:19 pm
by RobertGonzalez
Charles256 wrote:i don't suppose..

Code: Select all

$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = mysqli_query($db, $query);
should be..

Code: Select all

$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
  $result = mysqli_query($query,$db);
or am i being silly? test it..
The manual shows that the link comes first, then the query. Seems sort of odd, but that is how it is shown at php.net.