MySQL Forums
Forum List  »  Newbie

Re: Proper syntax for multiple query string
Posted by: Ivan D
Date: May 25, 2005 07:44PM

It would be usefull if you could send a bunch of queries all at once and save yourself the extra network traffic of sending them one by one.
Or if you wanted to simplify the proccess of using data one time and one time only (like using arecord from a table and then delete it right away before another query could mistake that same record as still usable)

Here's a practical example.

You have tables with auto incrementing ids, these ids could also be foreign keys to other tables.

As records get deleted you get gaps in the ids. They can potentially be recycled.
You could copy over the data and re-assign the ids, but then records from other tables which relied on that id would be messed up, or you'd have to change those too (which could take lots of time)

Setting the Auto_Increment to insert where there's a gap doesnt work, it will just set the number to one higher than the highest auto_incrementing id.
(but you could make the foreign key independant of the auto_increment)


Ok, so you add a field to the tables where you expect records will be deleted on and off, lets call it rcl (recycle). It will either be 1(i can be reused) or 0(dont reuse me);

Table Gallery [id] [rcl] [otherinfo]
Table GalImg [id] [rcl] [gallery_id] [otherinfo]

When you delete a Gallery record, you dont delete it, you just issue an UPDATE which marks rcl to 1 and clear otherinfo. Then tell all images related to that Gallery to unlink from it, or you could delete them too.
This can be done in 2 separate queries, which is no porblem.

$query = "UPDATE gallery SET rcl=1,otherinfo='' WHERE id='$id'";
run it
$query = "UPDATE galimg SET gallery_id=0 WHERE gallery_id='$id'";
run it

Now someone wants to create a new gallery;
First try to create it in a reusable spot.

$ret = array()
$ret[success] = 0;
$ret[id_handle] = 0;

$query = "UPDATE gallery SET rcl=0, title='new' WHERE rcl=1 LIMIT 1";
if(querty($query) && mysql_affected_row() == 1) {
$ret[success] = 1;
$ret[id_handle] = "no way to get it";
else {
$query = "INSERT INTO gallery(title)VALUES('new')";
if(querty($query));
$ret[success] = 1;
$ret[id_handle] = mysql_insert_id();
}
return $ret;

Unfortunately when reusing a previously deleted ID you are blindly creating the records, you can't reliably retrieve the first reusable id and then update it because someone else might be updating it right after you retrieve it, and then you would overwrite their data. Too bad there is no mysql_last_update_id() method.
And too bad you cant do something like
$query = "SELECT id FROM gallery WHERE rcl=1 LIMIT1;"
$query .= "UPDATE gallery SET rcl=0, title='new' WHERE rcl=1 LIMIT 1";
run it and also get the reused id (if there was one available).

Sometimes you dont really care about the id, you just want the new record in there.
I supose a work around would be to provide another unique field, and when you update you could set that field and then find the record id through that.

Another example.

SELECT user,pass,logged FROM admin WHERE user='$user';
UPDATE admin SET logged=1 WHERE user='$user';

If you can process both of those at once you would ensure that the admin can only log once at a time. Ofcourse you can't, I still made a workaround to ensure they are only logged once, but it took alot more coding.

Maybe there's simpler solutions to all these problems, but I havn't discovered them yet.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.