PHP/MySQL query not working with no error ← (PHP, MySQL)
I have written that seems to not be working, but MySQL does not return any error. It is supposed to get data from database1.table to update database2.table.column
<?php
$dbh1 = mysql_connect('localhost', 'tendesig_zink', 'password') or die("Unable to connect to MySQL");
$dbh2 = mysql_connect('localhost', 'tendesig_zink', 'password', true) or die("Unable to connect to MySQL");
mysql_select_db('tendesig_zink_dev', $dbh1);
mysql_select_db('tendesig_zink_production', $dbh2);
$query = " UPDATE
tendesig_zink_dev.euid0_hikashop_product,
tendeig_zink_production.euid0_hikashop_product
SET
tendesig_zink_dev.euid0_hikashop_product.product_quantity = tendesig_zink_production.euid0_hikashop_product.product_quantity
WHERE
tendesig_zink_dev.euid0_hikashop_product.product_id = tendesig_zink_production.euid0_hikashop_product.product_id";
if (mysql_query($query, $dbh1 ))
{
echo "Record inserted";
}
else
{
echo "Error inserting record: " . mysql_error();
}
?>
Answer
Solution:
The manual page for
link_identifierThe MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
So it's reading errors from $dbh2, which is the last connection you've opened. However, you never run any query on $dbh2:
mysql_query($query, $dbh1 )
Thus you get no errors because you are reading errors from the wrong connection.
The solution is to be explicit:
mysql_error($dbh1)
As about what you're trying to accomplish, while you can open as many connections as you want, those connections won't merge as you seem to expect: they're independent sessions to all effects.
All your tables are on the same server and you connect with the same users, there's absolutely no need to even use two connections anyway.
Answer
Solution:
You can't just issue a cross-database update statement from PHP like that!
You will need to execute a query to read data from the source db (execute that on the source database connection: $dbh2 in your example) and then separately write and execute a query to insert/update the target database (execute the insert/update query on the target database connection: $dbh1 in your example).
Essentially, you'll end up with a loop that reads data from the source, and executes the update query on each iteration, for each value you're reading from the source.
Answer
Solution:
I appreciate everyone's help/banter, here is what finally worked for me.
<?php
$dba = mysqli_connect('localhost', 'tendesig_zink', 'pswd', 'tendesig_zink_production') or die("Unable to connect to MySQL");
$query = " UPDATE
tendesig_zink_dev.euid0_hikashop_product, tendesig_zink_production.euid0_hikashop_product
SET
tendesig_zink_dev.euid0_hikashop_product.product_quantity = tendesig_zink_production.euid0_hikashop_product.product_quantity
WHERE
tendesig_zink_dev.euid0_hikashop_product.product_id = tendesig_zink_production.euid0_hikashop_product.product_id";
if (mysqli_query($dba, $query))
{
echo "Records inserted";
}
else
{
echo "Error inserting records: " . mysqli_error($dba);
}
?>