[PHP] Help with string syntax

[whitebread 54]

Hey all;

I'm not terribly familiar with PHP, but for the most part I seem to be muddling through. I have encountered 1 error which I can't seem to figure out how to fix.

Here's the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

I believe the error is caused by a problem with my SQL query:

SELECT * FROM tbMarketingHistory WHERE OfferID=\'\'

Here's the actual line of code where the query is generated:

$query  = "SELECT * FROM tbMarketingHistory WHERE OfferID='".$OfferID."'";

$query is, obviously, the string variable which stores the SQL statement. $OfferID is an integer which acts as a unique identifier (each row has a different OfferID) in the MySQL database.

[Mathachew 149]

It looks like you're performing mysql_escape_string (or mysql_real_escape_string) on the entire query instead of the data itself that is being passed, hence the \'\'. Perform your escapes on the data and then add them to the query and see what that gets you.

[morficus 0]

after you declare $query, try doing

echo $query;

and see what its' actually constructing

also... at some point... you should have something that looks like this:

$queryResult = mysqli_query($db, $query);<BR><BR>if (mysqli_num_rows($queryResult) )<BR>{<BR>		  $resultRow = mysqli_fetch_array("Name_Of_A_Column");<BR>}

the error is saying that something is wrong with mysqli_fetch_array()... so you are prolly giving it something that is not a column in the DB (remember, it's case sensitive)

post the rest of the code if you still can figure it out

edit:

my mistake...

you're using mysql_fetch_array()... not mysqli_fetch_array()...

mysql_fetch_array() takes a result set... so looking at my example above... it would take the variable called $queryResult

Edited May 30, 2008 by morficus

[whitebread 54]

^ I already tried the echo $query; thing. That's how I got some information.

Here's the whole code. Again -- I am no PHP expert! I basically did a Google search and tried to adapt code I found.

<?
  $dbhost = '**.**.**.**';
  $dbuser = '*********';
  $dbpass = '*******';
  $OfferID = $_POST["id"];
  $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error: '.mysql_error ());

  mysql_select_db('***********');
  $query  = "SELECT * FROM tbMarketingHistory WHERE OfferID='".$OfferID."'";
  echo $query;
  $result = mysql_query($query);

  while($row = mysql_fetch_array($result, MYSQL_ASSOC))
  {
	$Chan = $row['Channel'];
	if ($Chan=="S") {
	  $Chan = "SMS";
	}
	elseif ($Chan=="M") {
	  $Chan = "Mail";
	}
	elseif ($Chan=="E") {
	  $Chan = "Email";
	}
	echo "<tr><td valign=top><div align=center><a href=viewofferdetail.php?id={$row['OfferID']}>{$row['OfferID']}</a></div></td><td valign=top><div align=center>{$row['DropDate']}</div></td><td valign=top>{$row['Title']}</td><td valign=top><div align=center>$Chan</div></td></tr>";
	$OffDet = $row['Details'];
  }
  ?>

[morficus 0]

there could be zero rows that match the query..... so mysql_query() is prolly returning "false" in place of a result set

try using mysql_num_rows($result) to see how many rows are returned from the select. chances are that it's zero

echo mysql_num_rows($result);

edit:

idk how much you plan to get into PHP... but using mysql_num_rows() or mysql_affected_rows() (after an insert or update) is usually a good practice just to make sure that nothing went wrong.

edit2:

just a few stuff I just nocited:

1- you might want to use mysql_fetch_assoc()

2- in the future, you may want to use mysqli_connect() and everything mysqli_* in place of mysql_*, since mysqli adds some extra goodness(link)

Edited May 30, 2008 by morficus

[whitebread 54]

Are you guys sure the error is not with my SQL statement string itself? The statement the code creates outputs as:

SELECT * FROM tbMarketingHistory WHERE OfferID=''

I think the problem has to do with the way it generates the string:

$query  = "SELECT * FROM tbMarketingHistory WHERE OfferID='".$OfferID."'";

EDIT: I edited the code to:

$query  = "SELECT * FROM tbMarketingHistory WHERE OfferID='10394'";

(removing the ".$OfferID."' part. it works exactly as intended. Thus, the problem MUST be with my string syntax for the query, and not the rest of the code.

[Mathachew 149]

$OfferID must be blank if you're getting OfferID=''

I notice you're using $_POST and not $_GET, is that correct?

[whitebread 54]

^ Pfft! I can't even believe the error was that dumb. LOL.......... Thanks! :D

[Mathachew 149]

Anytime ;)