Help with php/MySql query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
colorproof
Forum Newbie
Posts: 9
Joined: Fri Oct 21, 2005 12:38 pm
Location: Oregon

Help with php/MySql query

Post 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.
Last edited by colorproof on Fri Oct 21, 2005 6:10 pm, edited 2 times in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
colorproof
Forum Newbie
Posts: 9
Joined: Fri Oct 21, 2005 12:38 pm
Location: Oregon

Post 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.
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

just your tags around your php from code to php tags and I'll take a look at the source... :-D
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
colorproof
Forum Newbie
Posts: 9
Joined: Fri Oct 21, 2005 12:38 pm
Location: Oregon

Post 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!
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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...
User avatar
colorproof
Forum Newbie
Posts: 9
Joined: Fri Oct 21, 2005 12:38 pm
Location: Oregon

Post 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!

:)
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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))
User avatar
colorproof
Forum Newbie
Posts: 9
Joined: Fri Oct 21, 2005 12:38 pm
Location: Oregon

Post by colorproof »

Alright, I have changed the code to php, sorry about that!

:D
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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
User avatar
colorproof
Forum Newbie
Posts: 9
Joined: Fri Oct 21, 2005 12:38 pm
Location: Oregon

Post 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!!!
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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..
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply