Query returns correct results in MySQL, but not in PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
esthermstrom
Forum Newbie
Posts: 9
Joined: Sat Apr 24, 2010 9:59 pm

Query returns correct results in MySQL, but not in PHP

Post by esthermstrom »

I'm a very new php/mysql developer, and although I've searched forums, I don't know that I'm searching for the right thing, so please bear with me if this is a common issue.

My table contains the following data:

[text]+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| thread_id | message_id | subject | body | posted_by | post_date | parent_id |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| 1 | 2 | New Discussion 1 | This is some text for discussion 1. | Esther Strom | 2010-04-24 20:21:43 | 0 |
| 1 | 3 | New Response to message 2 | This is some text for response 1. | Esther Strom | 2010-04-24 20:23:36 | 2 |
| 2 | 4 | New Thread 2 | This is some text for main 2. | Esther Strom | 2010-04-24 20:23:58 | 0 |
| 3 | 5 | New Discussion 3 | This is some text for discussion 3. | Esther Strom | 2010-04-24 20:32:47 | 0 |
| 1 | 6 | Another response to message 2 | This is some text for response 2. | Esther Strom | 2010-04-24 20:33:26 | 2 |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+[/text]

I have a very simple select query:

Code: Select all

SELECT * FROM discussions where thread_id=1 order by message_id asc
When I run this in my SQL gui (MySQL Control Center) it returns what I expect:
[text]+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| thread_id | message_id | subject | body | posted_by | post_date | parent_id |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+
| 1 | 2 | New Discussion 1 | This is some text for discussion 1. | Esther Strom | 2010-04-24 20:21:43 | 0 |
| 1 | 3 | New response to message 2 | This is some text for response 1. | Esther Strom | 2010-04-24 20:23:36 | 2 |
| 1 | 6 | Another response to message 2 | This is some text for response 2. | Esther Strom | 2010-04-24 20:33:26 | 2 |
+-----------+------------+------------------+-------------------------------------+--------------+---------------------+-----------+[/text]

However, when I use the same query inside my PHP code, it doesn't pull in the first item (the main message, which has a parent_id of 0.) The page is called from a link that contains an id parameter, like so:
[text]http://127.0.0.1/pcbug/viewDiscussion.php?id=1[/text]

Here's the code in viewDiscussion:

Code: Select all

<?php require_once("connectvars.php");  //contains db_ variables
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ( "Can't connect to the MySQL Server/Database" );

if ( isset($_GET['id']) && is_int((int)$_GET['id']) ) { 
	//we have an id in the URL
		$query = "SELECT * FROM discussions where thread_id=".$_GET['id'] . " order by message_id asc" ;
		$result = mysqli_query($dbc, $query) or die('Error querying database');
		$rows = mysqli_fetch_array($result);
		mysqli_close($dbc);
} //end if
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>pcbug - discussions - <?php echo  $rows['subject']; ?></title>
</head>
<body>

<?php
while ( $rows = mysqli_fetch_array($result) ) {	
        echo "<p>message id: " .$rows['message_id']. "<br/>";
        echo "posted by: " . $rows[ 'posted_by']. "<br/>";
        echo "posted on: " . $rows[ 'post_date']. "<br/>";
        echo "parent id: " . $rows[ 'parent_id']. "<br/>";
        echo "body: " . $rows[ 'body']. "</p>";
}
?>
</body>
</html>
This is the result I get (output HTML code):

message id: 3
posted by: Esther Strom
posted on: 2010-04-24 20:23:36
parent id: 2
body: This is some text for response 1.

message id: 6
posted by: Esther Strom
posted on: 2010-04-24 20:33:26
parent id: 2
body: This is some text for response 2.


There should be another chunk at the very beginning that looks like this:

message id: 2
posted by: Esther Strom
posted on: 2010-04-24 20:10:18
parent id: 0
body: This is some text for discussion 1.


Any ideas? I'm sure this is something very simple, but I have no idea where to even look for an answer.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Query returns correct results in MySQL, but not in PHP

Post by Darhazer »

Here you are discarding the first row:

Code: Select all

 $rows = mysqli_fetch_array($result);
                mysqli_close($dbc);
And later you are iterating trough the rest of the rows:

Code: Select all

while ( $rows = mysqli_fetch_array($result) ) { 
esthermstrom
Forum Newbie
Posts: 9
Joined: Sat Apr 24, 2010 9:59 pm

Re: Query returns correct results in MySQL, but not in PHP

Post by esthermstrom »

Thanks.
Post Reply