PHP MySQL Query Error - Only shows in my PHP Script not on direct Query?

[OldWest]

Here is my code:

// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
		"SET orig_code_1 = new_code_1,
     			 orig_code_2 = new_code_2" .
		"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records

 

This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

 

 

 

[Pikachu2000]

You're starting out with a SELECT query, then following it with UPDATE syntax . . .

[OldWest]

You're starting out with a SELECT query, then following it with UPDATE syntax . . .

 

The table has the name "update" in it separated by underscores... That would cause this?

[Pikachu2000]

No, look at your query string again.

 

SELECT . . . FROM . . . SET

 

Its either

 

SELECT . . . FROM

-or-

UPDATE . . . SET

[OldWest]

I'm really sorry, man. But I'm not getting this. I just tried 4 variations to no avail:

 

$query = "UPDATE codes_update_no_join_1b" .
		       "SET orig_code_1 = new_code_1,
     			               orig_code_2 = new_code_2" .
		"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";

 

UPDATE ... SET ... ( also tried "UPDATE FROM...")

[Pikachu2000]

I'm not sure what you're trying to do, but you have the syntax all hosed up. Proper UPDATE syntax is:

 

UPDATE `table` SET `field1` = 'value1', `field2` = 'value2' WHERE `some_field` = 'some_value'

 

What errors are you getting when you execute the query?

[OldWest]

Pikachu2000,

 

This is the original working query I was using to do this test. As you can see two tables were involved for the processing, but I am trying to get this query into one table thus what I have been trying to work out.

 

Here is the JOIN code:

 

UPDATE tableA AS a
  JOIN tableB AS b ON concat(a.field1, a.field2) = concat(b.field1, b.field2)
SET a.field1 = b.field3,
    a.field2 = b.field4

 

I am trying to achieve the same result with a single table - that's all I'm trying to do. I'm losing my mind over it because phpMyAdmin seems to process queries that won't work when written in my php script - very confusing!

 

Do you have any idea why this is happening?

 

 

 

 

 

[Pikachu2000]

I've never had a query fail in a php script that executed in phpMyAdmin, unless I had something misquoted, etc. Are you echoing the output of mysql_error(), and echoing the query string when it fails when executed from within php?

[OldWest]

I would lay money on my errors ...

 

Here is a snippet of my code now:

 

// Get all the data from the "example" table
$result = mysql_query("UPDATE `codes_update_no_join_1c` SET `orig_code_1` = `new_code_1`, `orig_code_2` = `new_code_2` WHERE `old_code_1` = `orig_code_1`") or die(mysql_error());  

// START :: Displays Record Count of Query
echo "There are: <b>" . mysql_num_rows($result) . "</b> results for that query.<br /><br />";
// END :: Displays Record Count of Query

 

The data in my table seems to be getting updated, but upon checking for accuracy, something is not right.. Here are the errors that are being output now:

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\php_practice\0\ttr_mysql_output_4.php on line 23
There are: results for that query.


Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\php_practice\0\ttr_mysql_output_4.php on line 37

 

 

[Pikachu2000]

For an INSERT or UPDATE query, you need to use mysql_affected_rows(). mysql_num_rows() is only used with queries that return a result set, such as a SELECT query . . .

[OldWest]

Updated code to:

 

// START :: Displays Record Count of Query
echo "There are: <b>" . mysql_affected_rows($result) . "</b> results for that query.<br /><br />";
// END :: Displays Record Count of Query

 

Same error? Weird.

[trq]

Obviously some other code is triggering the error then.

[OldWest]

Obviously some other code is triggering the error then.

 

It must be the SQL I am using because that is all that I changed. Here is the SQL I am using:

 

// START :: Query to replace matches
$result = mysql_query("UPDATE `codes_update_no_join_1d`
				   SET `orig_code_1` = `new_code_1`, `orig_code_2` = `new_code_2`
				   WHERE `old_code_1` = `orig_code_1`") or die(mysql_error());
// END :: Query to replace matches

 

[trq]

The error pretty clearly states that there is problems with whatever you are passing to mysql_num_rows() and mysql_fetch_array().

 

Can we see ALL your code? This is going nowhere fast.

[trq]

Your query is no good also....

 

$result = mysql_query("UPDATE `codes_update_no_join_1d`
   SET `orig_code_1` = 'new_code_1', `orig_code_2` = 'new_code_2'
   WHERE `old_code_1` = 'orig_code_1'") or die(mysql_error());

[OldWest]

Here is my entire script.

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Matching Codes & Replacce w/ MySQL</title>
</head>
<body>
<style type="text/css">
.changedClassCSS {
	background-color: #FCF;
}
</style>
<h1>Records Output</h1>
<?php
// START :: MySQL Connection
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("abc") or die(mysql_error());
// END :: MySQL Connection

// START :: Query to replace matches
$result = mysql_query("UPDATE `codes_update_no_join_1d`
				   SET `orig_code_1` = `new_code_1`, `orig_code_2` = `new_code_2`
				   WHERE `old_code_1` = `orig_code_1`") or die(mysql_error());
// END :: Query to replace matches

// START :: Displays Record Count of Query
echo "There are: <b>" . mysql_affected_rows($result) . "</b> results for that query.<br /><br />";
// END :: Displays Record Count of Query

echo "<table border='1' cellpadding='3' width='100%'>";
echo "
<tr>
<th>Original Code 1</th>
<th>Original Code 2</th>
<th>Old Code 1</th>
<th>Old Code 2</th> 
<th>New Code 1</th> 
<th>New Code 2</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array($result)) {
// Print out the contents of each row into a table

//START :: If to find matching criteria class it w/ bg color
if ($row['orig_code_1'] > '10000') {
		$changedClass = "changedClassCSS";
} else {
		$changedClass = "";
};
//END :: If to find matching criteria class it w/ bg color

echo "<tr><td class=\"$changedClass\">"; 
echo $row['orig_code_1'];
echo "</td><td>"; 
echo $row['orig_code_2'];
echo "</td>"; 

echo "<td>"; 
echo $row['old_code_1'];
echo "</td><td>"; 
echo $row['old_code_2'];
echo "</td>"; 

echo "<td>"; 
echo $row['new_code_1'];
echo "</td><td>"; 
echo $row['new_code_2'];
echo "</td>"; 
} 

echo "</table>";
?>
</body>
</html>

[trq]

Your executing an UPDATE query not a SELECT query.

 

UPDATE queries are for editing data, not retrieving & displaying it. Really, what exactly are you trying to do?

[OldWest]

Your executing an UPDATE query not a SELECT query.

 

UPDATE queries are for editing data, not retrieving & displaying it. Really, what exactly are you trying to do?

 

All I am trying to do is user ONE table, instead of TWO to perform this query:

 

UPDATE tableA AS a
        JOIN tableB AS b ON concat(a.field1, a.field2) = concat(b.field1, b.field2)
                SET a.field1 = b.field3,
                       a.field2 = b.field4

 

And during the process, I would like to 1) know how many records are updated, and 2) mark the updated record row with a background color ( I've got this part 1/2 working)..

 

[trq]

Your not executing that query anywhere. And even if you did, you would need to use a separate SELECT query to display any of this data.

[OldWest]

Your not executing that query anywhere. And even if you did, you would need to use a separate SELECT query to display any of this data.

 

Maybe I am not totally sure what you are referring to, but isn't this executing that original query?

 

while($row = mysql_fetch_array($result)

 

And from what I understand, I will need to run a 2nd SELECT query in order to display the results.. Is it normal to have the UPDATE and SELECT all in one $result? Or is this normally two totally separate queries?

 

Thanks for your patience, I am just trying to get some fundamental understanding, so I can make this script do what I want.

 

[trq]

mysql_query() executes queries. The code you have posted is used to loop through the results of a SELECT query. You are executing an UPDATE query which does not return any result resource.

[OldWest]

mysql_query() executes queries. The code you have posted is used to loop through the results of a SELECT query. You are executing an UPDATE query which does not return any result resource.

 

Thanks, Thorpe

I was able to get it working like:

 

// START :: Create query to be displayed as final results of original codes table.
$result = mysql_query("SELECT * FROM orig_codes_1a") 
or die(mysql_error());  
// END :: Create query to be displayed as final results of original codes table.

// START :: Displays Record Count of Query
echo "<p>There are: <b>" . mysql_num_rows($result) . "</b> results for that query.</p>";
// END :: Displays Record Count of Query