[RESOLVED] Query Problems

[gobbles]

Hey All,

Im having major problems with some code of mine.

I know its sloppy and im only a newbie, but could someone help me out.

The code is as follows:

  $id = $_GET['id'];

  $managecount_query = "SELECT id FROM listings WHERE id = '$id'";
  $managecount = mysql_query($managecount_query);
  $managerows = mysql_num_rows($managecount);

if($managerows > 0) {
  $mainquery = "SELECT advertisers.venue_name, listings.listing_id, listings.headline
                FROM listings
                INNER JOIN advertisers
                ON advertisers.id = listings.id";
  $mainresults = mysql_query($mainquery) or die(mysql_error());
  $mainrow = mysql_fetch_array($mainresults);
} else {
  $mainquery = "SELECT * FROM advertisers WHERE id = '$id'";
  $mainresults = mysql_query($mainquery) or die(mysql_error());
  $mainrow = mysql_fetch_array($mainresults);
}

A little bit further down the page i try to call the results again .. there are 2 results in $managerows but its only returning one of them

<?php
$maincount = 0;
  while($testing = mysql_fetch_array($mainresults, MYSQL_NUM) ) {
    echo "<tr valign=\"top\">\n";
    echo "<td width=\"11%\" bgcolor=\"#F9F9F9\" align=\"center\">".$mainrow['headline']."</td>\n";
    $maincount++;
  }
?>

Thanks all

[laserlight]

That is due to the earlier mysql_fetch_array() call. As such, the first row in the result set has been retrieved, so the loop starts at the second row.

[gobbles]

I took out the earlier mysql_fetch_array's and changed the line in the while loop to

echo "<td width=\"11%\" bgcolor=\"#F9F9F9\" align=\"center\">".$testing['venue_name']."</td>\n";

But its still not returning the values

[laserlight]

Does this work?

$mainquery = "SELECT * FROM advertisers WHERE id = '$id'";
$mainresults = mysql_query($mainquery) or die(mysql_error());
while($testing = mysql_fetch_array($mainresults, MYSQL_NUM) ) {
    echo $testing['venue_name'] . '<br />';
}

One good debugging technique is to write the smallest and simplest code that demonstrates the problem. Even if you still cannot diagnose the problem, it becomes easier for someone else to find it.

[gobbles]

Hmmm .. Thats interesting ... Its only returning the <br />

[laserlight]

Change it to:

$mainquery = "SELECT * FROM advertisers WHERE id = '$id'";
$mainresults = mysql_query($mainquery) or die(mysql_error());
while($testing = mysql_fetch_array($mainresults, MYSQL_NUM) ) {
    print_r($testing);
    echo '<br />';
}

This helps you to see what mysql_fetch_array() really returns, and from there determine where you are going wrong.

[gobbles]

Ok ... That returned all the values in the array .. the i changed back to the previous echo $testing['venue_name'] . '<br />'; and changed the venue_name to 0 and it returned the right value, so does the MYSQL_NUM mean that i have to use numbers to output the results?

[laserlight]

Oh, yeah, I did not notice that. You could read the PHP Manual on [man]mysql_fetch_array/man. I would probably just use [man]mysql_fetch_assoc/man in your case.

[gobbles]

I just took out the MYSQL_NUM and it returned everything i wanted.

Thank you so much for your help

[laserlight]

I just took out the MYSQL_NUM and it returned everything i wanted.

It also returned with the numeric indices, so your array of the result row is larger than it needs to be.

Thank you so much for your help

You're welcome 🙂
Remember to mark this thread resolved (if it is).