[SOLVED] Successful MySQL query via PHP crashes Apache

[eagledare]

Hi everyone. I'm new to the forum and working my way through the PHP and MySQL Web Development book by Luke Welling and Laura Thompson. I only mention the book in case others might have gotten stuck on the same item.

 

I just got to the part of the book that teaches you how to use MySQL queries via php. Any time I run a query that I believe is successful Apache crashes and restarts. I have tried commenting various lines of code in my php and I know that this issue only happens when the query results are being parsed (specifically on the fetch_assoc/fetch_num line). I can connect to the database and even display the number of rows, etc. but when I perform the query and parse the result I always get a crash.

 

I've tried using several different methods to parse the result, including mysqli_fetch_assoc, mysqli_fetch_num, and mysql_fetch_num. All result in crashes.

 

I'm running php 5.2.10, MySQL server 5.1, and Apache 2.2 on a Windows XP box with plenty of power/ram. I've taken all of the obvious steps such as rebooting and recreating the php code and nothing has helped.

 

Thanks in advance.

 

Code:

 

<?php

phpinfo();

  // create short variable names

  $searchtype=$_POST['searchtype'];

  $searchterm=trim($_POST['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 = new mysql('localhost', 'bookorama', 'bookorama123', 'books');

 

  if (mysql_connect_errno()) {

    echo 'Error: Could not connect to database.  Please try again later.';

    exit;

  }

 

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

  $result = $db->query($query);

 

  $num_results = $result->num_rows;

 

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

 

  for ($i=0; $i <$num_results; $i++) {

    $row = $result->fetch_num();

    echo "<p><strong>".($i+1).". Title: ";

    echo htmlspecialchars(stripslashes($row[0]));

    echo "</strong><br />Author: ";

    echo stripslashes($row[1]);

    echo "<br />ISBN: ";

    echo stripslashes($row[2]);

    echo "<br />Price: ";

    echo stripslashes($row[3]);

    echo "</p>";

  }

 

  $result->free();

  $db->close();

 

?>

 

 

[PFMaBiSmAd]

mysql is not a class and mysql_connect_errno() does not exist, which is probably why your code has an @ in it to suppress error messages. mysqli is the name of the class and mysqli_connect_errno() is the name of the function.

 

To start with, you need to set error_reporting to E_ALL and display_errors to ON in your php.ini to get php to help you. Stop and start your web server to get any changed made to php.ini to take effect and use a phpinfo(); statement to confirm that the settings are actually what you think they are.

 

Edit: addslashes() does not escape all the special characters that can break a query. If get_magic_quotes_gpc() is TRUE, you should actually use stripslashes() to remove the escaping that magic_quotes_gpc added and then use mysqli_real_escape_string() to escape all the special characters.

[eagledare]

I updated the code to what I used originally, which includes the mysqli class. I also removed the @.

 

<?php

  // create short variable names

  $searchtype=$_POST['searchtype'];

  $searchterm=trim($_POST['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 = new mysqli('localhost', 'root', 'h3a1thcar3', 'dbawesome');

 

  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 = $db->query($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 $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>";

echo $row[1];

  }

 

  $result->free();

  $db->close();

 

?>

 

All of the error reporting options you described are turned on in the php.ini file. I do not get an error for php, just an instant Apache crash with a generic "Apache has encountered a problem" message.

 

If I comment out the line with the fetch_assoc I get the following output:

 

"Book-O-Rama Search Results

Number of books found: 4

 

1. Title:

Author:

ISBN:

Price:

 

2. Title:

Author:

ISBN:

Price:

 

3. Title:

Author:

ISBN:

Price:

 

4. Title:

Author:

ISBN:

Price: "

 

It's something about that line but I have no idea what alternative to try  :'(

[jonsjava]

how many items are in your database? have you thought of limiting the query to....oh, say 100 items?  just add "LIMIT 100" to the end of the query.  I'm just curious if you're getting so many results that it's crashing the server.

[PFMaBiSmAd]

Web server crashes like this are usually due to miss-matched builds of php, where different parts are of different versions. The web server error log file probably contains more information about the problem causing the crash.

 

What method did you use to install php and enable the msyql/mysqli extensions?

[eagledare]

The table I'm hitting only has 4 records.

 

I used the zipped php package and installed manually. I just un-commented the lines in the php.ini for the modules I wanted to use and made sure the corresponding files were in the php\ext folder.

 

I just initiated the crash again and looked at the error log for Apache. This is all I found:

 

"

[Tue Aug 04 13:07:06 2009] [notice] Parent: child process exited with status 3221225477 -- Restarting.

[Tue Aug 04 13:07:07 2009] [notice] Apache/2.2.11 (Win32) PHP/5.2.10 configured -- resuming normal operations

[Tue Aug 04 13:07:07 2009] [notice] Server built: Dec 10 2008 00:10:06

[Tue Aug 04 13:07:07 2009] [notice] Parent: Created child process 1476

[Tue Aug 04 13:07:07 2009] [notice] Child 1476: Child process is running

[Tue Aug 04 13:07:07 2009] [notice] Child 1476: Acquired the start mutex.

[Tue Aug 04 13:07:07 2009] [notice] Child 1476: Starting 64 worker threads.

[Tue Aug 04 13:07:07 2009] [notice] Child 1476: Starting thread to listen on port 80."

[PFMaBiSmAd]

Searching for the first error line produces a huge number of results - http://www.apachelounge.com/viewtopic.php?t=2856

[eagledare]

Per that link copying the libmysql.dll into the apache\bin folder and restarting Apache fixed the issue.

 

THANK YOU!!!