im having a pretty weird problem with php and mysql. Im doing the following in my php script:
$query1 = "SELECT * FROM ".$src;
$ans1 = mysql_query($query1,$vitoquenId);
echo "<p>Query: \"".$query1."\"<br/>Error: \"".mysql_error()."\"<br/>Total rows extracted: ".mysql_num_rows($ans1)."</p>";
when doing this, this is the output:
Query: "SELECT * FROM Almacen"
Error: ""
Total rows extracted: 0
the thing is, im not supposed to get 0 rows, and the weird thing is that no error is reported. I granted all the permissions for my user, and even tried connecting to the database as root user, but got the same behavior.
When doing the query directly in the mysql command line im getting the following:
mysql> SELECT * FROM Almacen;
+----+-------------+------------+--------+
| id | nombre | tipo | status |
+----+-------------+------------+--------+
| 1 | Tienda | Principal | Activo |
| 2 | Dep�sito | Secundario | Activo |
| 3 | Chaguaramos | Secundario | Activo |
+----+-------------+------------+--------+
3 rows in set (0.00 sec)
Ive tried using the xampp server, and also a configured-by-myself server, and got the same behavior. Ive checked every log Ive thought of, configured mysql to log every query and nothing abnormal seems to happen.
the mysql log shows the following:
110802 16:21:49 101 Connect victor@localhost on
101 Init DB saw
101 Init DB sawprueba
101 Query SELECT * FROM Almacen
101 Quit
the apache log doesnt show anything, neither the site specific log. Please, any clue trying to decipher this would be very apreciated!!! thanks!!! =)
EDIT:
I found the problem. It has something to do with aefxx's answer.
The program im writing is a migration tool. It extracts info from one database, processes it, and inserts it into another db. To do so, i need to connect to two databases. I make the connection through two classes using the singleton pattern as suggested in the php documentation. You can see the connection file in the github repo.
to make the connection to the db, i call this class like this:
$vitoquen = Vitoquen::singleton();
$newVitoquen = NewVitoquen::singleton();
$vitoquenId = $vitoquen->getId();
$newVitoquenId = $newVitoquen->getId();
What is happening is that for some reason, it does not create two separate connections, but instead create just one connection, and use the second db, the new one, which is empty. I got the clue when i saw the mysql log:
110802 16:21:49 101 Connect victor@localhost on
101 Init DB saw
101 Init DB sawprueba
101 Query SELECT * FROM Almacen
101 Quit
it shows 101 Init DB twice; one for saw and other for sawprueba. Now I dont understand why it does not create two separate connections... any suggestions???
thank you very much for your help!!! =)
EDIT:
I cant self answer yet, but this is the solution to my problem. Thanks anyways to those who took the time of reading (and some answering) this!
As seen in the documentation of php mysql_connect() function, it receives an optional parameter called new_link, which is false by default.
new_link
If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. In SQL safe mode, this parameter is ignored.
so, i just needed to add a true as an additional last parameter to the call to mysql_connect() in both connection classes. SOLVED =)
Did you select the proper database (also called the scheme) when connecting to your DBMS?
Example:
mysql_select_db("mydatabase", $link);
Let's add some minimal error handling and an extra query to see what mysql "thinks" about the table in question.
$query1 = "SHOW TABLE STATUS LIKE '$src'";
echo '<pre>Debug: query1=', $query1, "</pre>
";
$ans1 = mysql_query($query1, $vitoquenId)
or die(mysql_error());
while ( false!==($row=mysql_fetch_assoc($ans1)) ) {
foreach( $row as $key=>$value ) {
echo $key,'="', $value, '" ';
}
echo "<br />
";
}
$query1 = "SELECT * FROM ".$src;
echo '<pre>Debug: query1=', $query1, "</pre>
";
$ans1 = mysql_query($query1,$vitoquenId)
or die(mysql_error());
echo "<p>Query: \"".$query1."\"<br/>Error: \"".mysql_error()."\"<br/>Total rows extracted: ".mysql_num_rows($ans1)."</p>";