Database result not surviving subsequent query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Database result not surviving subsequent query?

Post by Bill H »

The following is deleting multiple records from the "Main" database, but only inserting one new record in the "ObsMain" database.

Code: Select all

$Dat = sprintf("%d-%02d-01",$Yr,$Mth);
$Var = ($_POST['Lnum'] > 0) ? $_POST['Lnum'] : $_POST['Inum'];   // doing client or location
$Do = ($_POST['Lnum'] > 0) ? "lref" : "iref";                    // doing client or location

mysql_select_db("Main", $Link);                   // select the main database
$Query = "SELECT * FROM Staff WHERE $Do=$Var AND YEAR(Date)>2000 AND Date<'$Dat'";
$res = mysql_query($Query, $Link);
mysql_select_db("ObsMain", $Link);                // select the reserve database
while ($row = mysql_fetch_array($res))
{
     $i = 0 + $row['id'];
     $c = 0 + $row['iref'];
     $l = 0 + $row['lref'];

     $Query = "INSERT INTO Staff (id,iref,lref) VALUES ($i,$c,$l)";
     mysql_query($Query, $Link);
}
mysql_select_db("Main", $Link);                   // reselect the main database

$Query = "DELETE FROM Staff WHERE $Do=$Var AND YEAR(Date)>2000 AND Date<'$Dat'";
mysql_query($Query, $Link);
Is the $res not surviving for some reason, or what?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

try

Code: Select all

$Dat = sprintf("%d-%02d-01",$Yr,$Mth);
$Var = ($_POST['Lnum'] > 0) ? $_POST['Lnum'] : $_POST['Inum'];   // doing client or location
$Do = ($_POST['Lnum'] > 0) ? "lref" : "iref";                    // doing client or location

mysql_select_db("Main", $Link) or die(mysql_error());                   // select the main database
$Query = "SELECT * FROM Staff WHERE $Do=$Var AND YEAR(Date)>2000 AND Date<'$Dat'";
$res = mysql_query($Query, $Link) or die(mysql_error().': '.$Query);
mysql_select_db("ObsMain", $Link) or die(mysql_error());;                // select the reserve database
while ($row = mysql_fetch_array($res))
{
   $i = 0 + $row['id'];
   $c = 0 + $row['iref'];
   $l = 0 + $row['lref'];

   $Query = "INSERT INTO Staff (id,iref,lref) VALUES ($i,$c,$l)";
   mysql_query($Query, $Link) or die(mysql_error().': '.$Query);
   echo '</p>', $Query, ', affected rows: ', mysql_affected_rows($Link), "</p>\n";
}
mysql_select_db("Main", $Link) or die(mysql_error());                   // reselect the main database

$Query = "DELETE FROM Staff WHERE $Do=$Var AND YEAR(Date)>2000 AND Date<'$Dat'";
mysql_query($Query, $Link) or die(mysql_error().': '.$Query);
echo '</p>', $Query, ', affected rows: ', mysql_affected_rows($Link), "</p>\n";
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Um, I have already determined that it inserted one single record into the ObsMain database, and deleted 24 records from the Main database.

Code: Select all

"WHERE $Do=$Var AND YEAR(Date)>2000 AND Date<'$Dat'"
If that selects 24 records for deletion, why does it not select 24 records to be inserted into the other database?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

If that selects 24 records for deletion, why does it not select 24 records to be inserted into the other database?
I don't know. That's why you should try the modified version and maybe post the output.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

It says that it affected 1 row, and then that it affected 24 rows, which comes as no surprise. The fact that the while loop iterates at all told me that the initial query did not fail, and I even added a mysql_num_rows() that tells me it is returning 24 rows, which also comes as no surprise. The fact that 24 records disappeared from the database also told me that the second major query did not fail. I didn't really need all the die() statements, because I knew the queries were executing and, sure enough, when I put them in none of them printed antyhing. The only real surprise, and my actual question is, when the query is returning 24 records, why is the while loop only executing one single time?

Is there something about the nature of the resource variable (in this case $res) than causes it to be reset when a query is executed even if no variable is specified? I cannot imagine that such would be the case, but I can't see any reason for the while loop to be terminating after a single iteration. There may be something simple that I am just being blind to.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

well, if it does not work with single link, try establishing two db links, one for select and another one for inserts. I remember I had read something about not being able to issue queries before last result set was fetched completely, but I believe it was about unbuffered queries which is not the case here.

Alternatively, you could fetch result set into an array and then iterate over the array inserting records as you see fit.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

I would do 2 connections to the databases

Code: Select all

$MainConnection = mysql_connect('host','user','pass');
mysql_select_db('main',$MainConnection);

$ReserveConnection = mysql_connect('host','user','pass');
mysql_select_db('reserve',$ReserveConnection );

//then use the link identifier in each query

//main query

$MainQuery = "SELECT * FROM blah";
$MainResult = mysql_query($MainQuery, $MainConnection);

//reserve query

$ReserveQuery = "SELECT * FROM whatever";
$ReserveResult = mysql_query($ReserveQuery,$ReserveConnection);

There is no need to keep swapping the database selected.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

How does the fact that I am using mysql_pconnect() affect that suggestion?

For one, I make that connection in a separate include file so that when I move to another host I can change the username and password in a single place and not have to search and replace in dozens of files.

Second, if I attempt another connection using mysql_pconnect() here it's going to use the same connection, and even using mysql_connect() will return the same link unless the "new_link" parameter is used. (But if I move to a diffierent host I would still have an issue).

I've been tied up with another issue, but I'm going to try fetching the result set into another array before selecting the other db, and I'll post back with how that works out.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

print_r() $row on each iteration to see what the values are. You may also want to consider escaping.. if you have an O'Neill or similar name your queries will fail.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Since the values are coming from the database would they not already be escaped?
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Why dont you give it a bash and find out. So what if you have to use the new_link parameter?

I've used this technique a few times and never had any issues.

And if you want to continue with pconnect why not set up another username and connect to the other pconnect with the username, voila a new connection
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

Why dont you give it a bash and find out. So what if you have to use the new_link parameter?
We've kind of gone into theory and design here, but...

1. If I make a second connection here then I've increased my maintenance and probability of error in the event of a move to a new server because I have to provide some master documentation that this connection exists and where to find it to change it.

2. If I put two connections in the include file then a dozen or so users are always making two connections when only one is needed, except at this instance which is used only by the manager and only once per month. My server host is going to be less than thrilled about that.

3. If I make a different include file which has two connections that would solve that, and documentation for the reason can be within the include file, but...

4. Creating a different user for this one purpose creates maintenance issues similar to number 1 which can't be documented in the database maintenance panel itself and so have to be documented elsewhere.

5. All of which are additional issues laying in wait for anyone taking over maintenance of the system when I retire, which is not that far away.

So if there is a way to do it which is self-evident within the scope of the task, and does not create additional complication with database connectivity and master documentation then that would be preferable to me and to my client. Fully fetching the result set into an array, temporary memory which self-destructs when the script terminates and which is not referenced anywhere in the system other than by the script that uses it, would seem to meet those criteria if it gets the job done, and preferable to creating additional database connections and users.

If that doesn't work, then I will "give it a bash."
Post Reply