Page 1 of 1
Multiple select queries
Posted: Wed Sep 28, 2005 6:40 am
by jurriemcflurrie
Hey I have a question about multiple select queries on one page... I don't like to do it because I think it will be slower, but I can't find out how to do it an other way. Joining tables is not always possible. Like a auth function, poll function, retrieve data and retrieving data within a loop.
So my question is, do I do things the right way or do I have to change my coding behavior? And if so, in what way exactly?
Posted: Wed Sep 28, 2005 6:54 am
by Jenk
It is possible, and is very common.
Just keep the connection open and it'll be speedy

Posted: Wed Sep 28, 2005 6:56 am
by jurriemcflurrie
Cool! It remains open till mysql_close right?
Thanks again!
Posted: Wed Sep 28, 2005 6:58 am
by Jenk
Yes and no.. dependant upon your timeout setting etc.
There is also mysql_pconnect which will maintain an open connection until mysql_close() is called

Posted: Wed Sep 28, 2005 7:13 am
by jurriemcflurrie
Ok then, I dont think my scripts will load that slow that the mysql connection times out
Although, I have once written a script wich took about 20 seconds to load. It checked if content was already there, deleted it if it was and inserted new data.. And that like 30 times.
Oh well, for now I don't have to worry about the selects then

thank u very much
Posted: Wed Sep 28, 2005 12:00 pm
by Skara
...If you mean you had a query that checked if it was there, then one that deleted it if it was, then inserted new data...
UPDATE tb SET field='newvalue' WHERE field='value';
Sorry, I just don't see how you would need that many queries and that it would take that long. O.o
Posted: Thu Sep 29, 2005 2:21 am
by jurriemcflurrie
Yes I wasn't that experienced to do it a better way. These days I would do it slightly different, how would you do the same thing? Just curious about other methods I don't know about.
The problem I face in these situation is always that you don't know if data already exists, so you have to check it first en when exists update it. Else just insert.
Posted: Thu Sep 29, 2005 4:38 am
by jurriemcflurrie
In addition to my previous post the way I do it these days is as follows:
Code: Select all
<?
function handle_form($project_id,$data)
{
foreach($data as $field => $value)
{
if(!$data_id=check_data($project_id,$field))
{
if($value)
{
insert_data($project_id,$field,$value);
}
}
else
{
if(!$value)
{
delete_data($data_id);
}
else
{
update_data($data_id,$value);
}
}
}
};
function check_data($project_id,$field)
{
$check=mysql_query("
SELECT *
FROM
prinfo_data
WHERE
project_id='$project_id' AND
field='$field'
")or die(''.mysql_error());
if(mysql_num_rows($check)==0)
{
return false;
}
else
{
$row=mysql_fetch_array($check);
return $row[data_id];
}
};
function insert_data($project_id,$field,$value)
{
$insert=mysql_query("
INSERT INTO
prinfo_data
VALUES (
'',
'$project_id',
'$field',
'$value')
")or die(''.mysql_error());
return true;
};
function update_data($data_id,$value)
{
$update=mysql_query("
UPDATE
prinfo_data
SET
value='$value'
WHERE
data_id='$data_id'
")or die(''.mysql_error());
return true;
};
function delete_data($data_id)
{
$delete=mysql_query("
DELETE FROM
prinfo_data
WHERE
data_id='$data_id'
")or die(''.mysql_error());
return true;
};
if($_POST)
{
handle_form($_SESSION[project_id],$_POST);
}
?>
Is this a good way or should I change something?
Posted: Thu Sep 29, 2005 5:12 am
by Jenk
Code: Select all
<?php
mysql_query("
UPDATE
prinfo_data
SET
value='$value'
WHERE
data_id='$data_id'
") or die(mysql_error());
if (mysql_affected_rows() < 1) {
mysql_query("
INSERT INTO
prinfo_data
VALUES (
'',
'$project_id',
'$field',
'$value')
") or die(mysql_error());
}
?>

Posted: Thu Sep 29, 2005 5:17 am
by jurriemcflurrie

Thnx! Never came up in mind that way
Posted: Fri Sep 30, 2005 7:42 am
by jurriemcflurrie

Jenk there is an problem with your method, that is
if data submitted already exists, mysql_affected_rows() will return 0 also

So there will be a row added.
Code: Select all
<?
function handle_form($project_id,$data)
{
foreach($data as $field => $value)
{
if(!update_data($project_id,$field,$value))
{
insert_data($project_id,$field,$value);
}
}
};
function insert_data($project_id,$field,$value)
{
$insert=mysql_query("
INSERT INTO
prinfo_data
VALUES (
'',
'$project_id',
'$field',
'$value')
")or die(''.mysql_error());
return true;
};
function update_data($project_id,$field,$value)
{
$update=mysql_query("
UPDATE
prinfo_data
SET
value='$value'
WHERE
project_id='$project_id' AND
field='$field'
")or die(''.mysql_error());
if(mysql_affected_rows()==0)
{
return false;
}
else
{
return true;
}
};
?>
So I can't check if data exists this way, unless I'm missing something again
