[SOLVED] mysql_num_rows(): supplied argument is not a valid MySQL result resource

DanielHardy · March 12, 2009

Hi,

I am gettng this error when trying to run a script that simply hanges the color of a checkbox when the user clicks the button. I know it is normally caused by missing ;'s or 's but I can't seethem in my script. Please Help?


<html>
<head>
<title>Regal Theatre - Online Ticket Booking System</title>
<style>
	* {
		font-size: 14px;
		font-family: arial;
	}
</style>
</head>
<body>

<center>
<br/>
<br/>
<br/>
<?php

if (isset($_POST['seats']))
{
	$user = $_COOKIE['ID_my_site'];

	$newStatusCode = $_POST['newStatusCode'];
	$oldStatusCode = $_POST['oldStatusCode'];

	// open database connection
	$linkID = @ mysql_connect("localhost", "0604274", "ah3482") or die("Could not connect to MySQL server");
	@ mysql_select_db("db0604274") or die("Could not select database");

	// prepare select statement
	$selectQuery = "SELECT rowId, columnId from seats where (";
	$count = 0;
	foreach($_POST['seats'] AS $seat) {
		if ($count > 0) {
			$selectQuery .= " || ";
		}
		$selectQuery .= " ( rowId = '" . substr($seat, 0, 1) . "'";
		$selectQuery .= " and columnId = " . substr($seat, 1) . " ) ";
		$count++;
	}
	$selectQuery .= " ) and status = $oldStatusCode";
	if ($oldStatusCode == 1) {
		$selectQuery .= " and updatedby = '$user'";
	}

	//echo $selectQuery;

	// execute select statement
	$result = mysql_query($selectQuery);
	//echo $result;

	$selectedSeats = mysql_num_rows($result);
	//echo "<br/>" . $selectedSeats;

	if ($selectedSeats != $count) {
		$problem = "<h3>There was a problem executing your request. No seat/s were updated.</h3>";
		$problem .= "Possible problems are:";
		$problem .= "<ul>";
		$problem .= "<li>Another process was able to book the same seat while you were still browsing.</li>";
		$problem .= "<li>You were trying to Confirm an unreserved Seat.</li>";
		$problem .= "<li>You were trying to Cancel an unreserved Seat.</li>";
		$problem .= "<li>You were trying to Reserve a reserved Seat.</li>";
		$problem .= "<li>There was a problem connecting to the database.</li>";
		$problem .= "</ul>";
		$problem .= "<a href='seats.php'>View Seat Plan</a>";
		die ($problem);
	}

	// prepare update statement
	$newStatusCode = $_POST['newStatusCode'];
	$oldStatusCode = $_POST['oldStatusCode'];

	$updateQuery = "UPDATE seats set status=$newStatusCode, updatedby='$user' where ( ";
	$count = 0;
	foreach($_POST['seats'] AS $seat) {
		if ($count > 0) {
			$updateQuery .= " || ";
		}
		$updateQuery .= " ( rowId = '" . substr($seat, 0, 1) . "'";
		$updateQuery .= " and columnId = " . substr($seat, 1) . " ) ";
		$count++;
	}
	$updateQuery .= " ) and status = $oldStatusCode";
	if ($oldStatusCode == 1) {
		$updateQuery .= " and updatedby = '$user'";
	}

	// perform update
	$result = mysql_query($updateQuery);
	$updatedSeats = mysql_affected_rows();

	if ($result && $updatedSeats == $count) {
		//$mysql->commit();
		echo "<h3>";
		echo "You have successfully updated $updatedSeats seat/s: ";
		echo "[";
		foreach($_POST['seats'] AS $seat) {
			$rowId = substr($seat, 0, 1);
			$columnId = substr($seat, 1);
			echo $rowId . $columnId . ", ";	
		}
		echo "]";
		echo "...</h3>";
	} else {
		//$mysql->rollback();
		echo "<h3>There was a problem executing your request. No seat/s were updated.</h3>";
		echo "Possible problems are:";
		echo "<ul>";
		echo "<li>Another process was able to book the same seat while you were still browsing.</li>";
		echo "<li>You were trying to Confirm an unreserved Seat.</li>";
		echo "<li>You were trying to Cancel an unreserved Seat.</li>";
		echo "<li>You were trying to Reserve a reserved Seat.</li>";
		echo "<li>There was a problem connecting to the database.</li>";
		echo "</ul>";
	}

	echo "<a href='seats.php'>View Seat Plan</a>";

	// Enable the autocommit feature
	//$mysqldb->autocommit(TRUE);

	// Recuperate the query resources
	//$result->free();

	mysql_close();
}
?>
</center>
</body>
</html>

Cheers

Dan

revraz · March 12, 2009

Means your query is failing. Echo it and see why.

Maq · March 12, 2009

Or you could see exactly what the error is by changing this line to:

      $result = mysql_query($selectQuery) or die(mysql_error());

DanielHardy · March 12, 2009

ok thanks for your messages

The error is the following


Unknown column 'cF5' in 'where clause'

Meaning the error is database based? Just checking as I am still a relative newbie to php. Thanks in advance

Mchl · March 12, 2009

Your query contains a column name 'cF5', but there is no such column in the actual table. Probably the code that's generating a query is flawed somewhere.

Maq · March 12, 2009

You may want to do what revraz suggested and echo out all of your queries to ensure all the column names you're referencing exist.

Looks like your error is triggered in the WHERE clause which is generated by the foreach loop from your POST vars. You can print the array of POST vars:

print_r($_POST);

or you can go back to the form where this information is coming from and see what's being inputted.

DanielHardy · March 12, 2009

Ok its querying fine


Array ( [oldStatusCode] => 1 [newStatusCode] => 2 [seats] => Array ( [0] => sA13 ) ) Unknown column 'A13' in 'where clause'

The column also exists in the mysql database.

Why is this happening!

Once again thanks in advance

Maq · March 12, 2009

Echo out your queries and tell me exactly what they output.

echo $selectQuery;
//and: 
echo $updateQuery;

Unknown column 'A13' in 'where clause'

It thinks that A13 is a column when you are actually asking - WHERE rowID = 'A13' - correct?

DanielHardy · March 12, 2009


Array ( [oldStatusCode] => 1 [newStatusCode] => 2 [seats] => Array ( [0] => sA13 ) ) SELECT rowId, columnId from seats where ( ( rowId = 's'SELECT rowId, columnId from seats where ( ( rowId = 's' and columnId = A13 ) Unknown column 'A13' in 'where clause'

Theres what you asked for.

It is recognising things fine except it doesnt seem to want to find the columnID's

Do i need to add a columnId = 'A13' ?

Mchl · March 12, 2009

change

$updateQuery .= " and columnId = " . substr($seat, 1) . " ) ";

to

$updateQuery .= " and columnId = '" . substr($seat, 1) . "' ) ";

(note: there are two of them)

DanielHardy · March 12, 2009

ok thanks I THINK that has solved it. However I wondered if you could help point out to me why the database is not updating (status changing from 0-1)?

I am just getting my error messages echoed back to me ( by my i mean my own coded ones)

revraz · March 12, 2009

Again, echo your update query and see what it contains.

Sign In

[SOLVED] mysql_num_rows(): supplied argument is not a valid MySQL result resource

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information