DaniWeb Forum Index > Web Development > PHP

Problem with where clause or ....??

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Nov 2008

Posts: 2

Reputation: CodeMama is an unknown quantity at this point

Solved Threads: 0

CodeMama

Offline

Newbie Poster

Problem with where clause or ....??

Nov 28th, 2008

Hi, I'm a newbie and am having a problem with either my where clause in my sql statement or something else, I think its the where because if I take that out the script works fine...
Here is the error I get:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\NewBeginnings\jobsOutput.php on line 12
Here is the code:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?php  include 'inc/dbconnOpen.php';
ini_set('error_reporting', E_ALL);
ini_set('display_errors', true);
 
 $sql = "SELECT * FROM `textads` WHERE `expos` GREATER THAN `xCount` ORDER BY RAND() LIMIT 3"; 
$result = mysql_query($sql);
 
echo "<b><center>Database Output</center></b><br><br>
<table border= 1 height=90px width=468px bgcolor=#cccccc>
<tr>";
 
while ($row = mysql_fetch_array($result)) {
    echo "
    <td>{$row['title']}<br>{$row['blurb']}<br>
    <A HREF='{$row['href']}'>{$row['href']}</a></td>";
    //Add to exposure count
    $views = $row['xCount'] + 1;
    mysql_query("UPDATE `textads` SET `xCount` = '{$views}' WHERE `ID` = '{$row['ID']}'");
}
 
echo " </tr>
</table>"; 
 
/* $num=mysql_num_rows($result);
 
mysql_close();
 
echo "<b><center>Database Output</center></b><br><br>";
 
$i=0;
while ($i < $num) {
 
$href=mysql_result($result,$i,"href");
$blurb=mysql_result($result,$i,"blurb");
$title=mysql_result($result,$i,"title");
 
   
echo "<table border= 1 height=90px width=468px bgcolor=#0066CC><tr><td>$title<br>$blurb<br>
<A HREF='$href'>$href</a></td><td>$title<br>$blurb<br>
<A HREF='$href'>$href</a></td><td>$title<br>$blurb<br>
<A HREF='$href'>$href</a></td></tr></table>";
 
$i++; */
 
/* }?> */
<?php  include 'inc/dbconnOpen.php';
ini_set('error_reporting', E_ALL);
ini_set('display_errors', true);

 $sql = "SELECT * FROM `textads` WHERE `expos` GREATER THAN `xCount` ORDER BY RAND() LIMIT 3"; 
$result = mysql_query($sql);

echo "<b><center>Database Output</center></b><br><br>
<table border= 1 height=90px width=468px bgcolor=#cccccc>
<tr>";

while ($row = mysql_fetch_array($result)) {
    echo "
    <td>{$row['title']}<br>{$row['blurb']}<br>
    <A HREF='{$row['href']}'>{$row['href']}</a></td>";
    //Add to exposure count
    $views = $row['xCount'] + 1;
    mysql_query("UPDATE `textads` SET `xCount` = '{$views}' WHERE `ID` = '{$row['ID']}'");
}

echo " </tr>
</table>"; 

/* $num=mysql_num_rows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$href=mysql_result($result,$i,"href");
$blurb=mysql_result($result,$i,"blurb");
$title=mysql_result($result,$i,"title");

   
echo "<table border= 1 height=90px width=468px bgcolor=#0066CC><tr><td>$title<br>$blurb<br>
<A HREF='$href'>$href</a></td><td>$title<br>$blurb<br>
<A HREF='$href'>$href</a></td><td>$title<br>$blurb<br>
<A HREF='$href'>$href</a></td></tr></table>";

$i++; */

/* }?> */

Thank For the Help and Clues!!
Terion

•

Join Date: Nov 2007

Posts: 596

Reputation: buddylee17 has a spectacular aura about

Solved Threads: 125

buddylee17 buddylee17 is offline

Offline

Posting Pro

Re: Problem with where clause or ....??

Nov 28th, 2008

Just use > instead of GREATER THAN. Since xCount is numeric, you also won't need quotes around it. So I think:

Help with Code Tags

php Syntax (Toggle Plain Text)

$sql = "SELECT * FROM `textads` WHERE `expos` > xCount ORDER BY RAND() LIMIT 3";

should work.

Lost time is never found again.
- Benjamin Franklin

•

Join Date: Oct 2006

Posts: 232

Reputation: Rhyan is an unknown quantity at this point

Solved Threads: 24

Rhyan

Offline

Posting Whiz in Training

Re: Problem with where clause or ....??

Nov 28th, 2008

Ok, the error says that no result has been returned from sql. This may be caused either by no data in tables or by a wrong sql statement.
I suggest you to put this line just after $result=mysql_query($sql);

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
if (!$result)
  {
die mysql_error();
  }
if (!$result)
  {
die mysql_error();
  }

If this does not return an error, then your tables are empty, or your sql is valid, but returns no records.
I think that you need to revise your sql statement though.
I don't really get the idea of this statement

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
SELECT * FROM `textads` WHERE `expos` GREATER THAN `xCount` ORDER BY RAND() LIMIT 3
SELECT * FROM `textads` WHERE `expos` GREATER THAN `xCount` ORDER BY RAND() LIMIT 3

First of all, I would remove the useless quotes around the column names and table name.
Then - greater than, you can just use '>' sign.
Next, what do you mean by setting an ordering which uses RAND() function - you want to randomize your results or what?
And last - LIMIT 3 - the limit clause normally should be used like
Limit "# of records to omit, # of records to return". So if you want to see only the first 3 records only, LIMIT 0,3 should be used instead.

" Of all the things I've lost,
I miss my mind the most...."
Mark Twain