Page 1 of 1

Query returns correct results in MySQL, but not in PHP

Posted: Sat Apr 24, 2010 10:49 pm
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.

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

Posted: Sun Apr 25, 2010 3:14 pm
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) ) { 

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

Posted: Mon Apr 26, 2010 12:10 am
by esthermstrom
Thanks.