Query only returns one result when using php but works on raw SQL

Question

I am dying here :-) Please help out! The following query gets different messages for different users from the same table based on current time.

$message = mysql_query("SELECT * FROM `table` 
           WHERE `Scheduled` <= DATE_ADD(UTC_TIMESTAMP(), INTERVAL 1 MINUTE)  
           AND `Status` != 'published'");

/** Kill db connection and exit script if no results are found **/

if (mysql_num_rows($message)== 0) { 
  mysql_close($db) && exit();
}
else {                                  
  while ($row = mysql_fetch_array($message))
  {
    $msg = $row["Messages"];
    $accnt = $row["Account"];

    {
    if ($accnt == 'Account1')
      echo $msg.$accnt;
    elseif ($accnt == 'Account2')
      echo $msg.$accnt; 
    else
      echo "Nothing Here!";
    }
  }
}

This only echos the first account, please help I have a headache. I have run this on the db directly and it works fine. I believe I am messing up in php

Your code has syntax errors, and could not even possibly run. What is the `{}` doing around the `if ($accnt ...)` stuff? — Marc B, Feb 14 '12 at 17:28
What exactly is your output? Is it Account1 followed by several `Nothing Here!`, or are you getting only one row back? — Michael Berkowski, Feb 14 '12 at 17:29
@MarcB That is weird, but it is valid. You can create an autonomous `{}` block. — Michael Berkowski, Feb 14 '12 at 17:30
Might want to consider using [PDO](http://php.net/manual/en/book.pdo.php) in the future, as well, if it is included in your PHP setup. — summea, Feb 14 '12 at 17:30
@Marc B The code runs fine Michael, I am only getting Account1 back — A.M.M, Feb 14 '12 at 17:58
Welcome to Stack Overflow! You are not doing any error checking in your query. You *need* to do that after a `mysql_query()` call. Otherwise, your script will break if the query fails. How to do this is outlined in the [manual on `mysql_query()`](http://php.net/mysql_query) or in this [reference question.](http://stackoverflow.com/questions/6198104/reference-what-is-a-perfect-code-sample-using-the-mysql-extension) — Pekka, Feb 29 '12 at 13:43

score 0 · Answer 1 · answered Feb 14 '12 at 17:37

0

I think you are getting non associative array. If you wanna assoc. array, you have to change it :

while ($row = mysql_fetch_array($message))

to

while ($row = mysql_fetch_assoc($message))

answered Feb 14 '12 at 17:37

mlinuxgada

580
3
7

The default for `mysql_fetch_array` returns an array that can be accessed associatively or numerically. – jmoerdyk Feb 14 '12 at 17:41
by default, `mysql_fetch_array()` retrieves both numeric and associative keys for all columns, effectively giving two copies of each column, so that isn't the issue here. – Michael Berkowski Feb 14 '12 at 17:41
Is there an error in my code and can foreach work in this query? – A.M.M Feb 14 '12 at 17:59
@user1207792 Tried changing and its still the same as Michael explained – A.M.M Feb 14 '12 at 18:00
there are weird curve brackets above the `if` statement and bellow echo `"Nothing Here!";` Try to remove them. – mlinuxgada Feb 14 '12 at 18:15
@mlinuxgada, I removed the curly braces form the script and its working fine now. I don't know why but it is working. Thank you – A.M.M Feb 17 '12 at 19:14

score 0 · Answer 2 · answered Feb 14 '12 at 18:05

//you have made a drastic mistake in your code check now it will work

if (mysql_num_rows($message)== 0) { 
  mysql_close($db) && exit();
}
else {                                  
  while ($row = mysql_fetch_array($message))
  {
    $msg = $row["Messages"];
    $accnt = $row["Account"];
    if ($accnt == 'Account1')
      echo $msg.$accnt;
    elseif ($accnt == 'Account2')
      echo $msg.$accnt; 
    else
      echo "Nothing Here!";
    }
  }

Query only returns one result when using php but works on raw SQL

2 Answers2