Multiple select queries
Moderator: General Moderators
- jurriemcflurrie
- Forum Commoner
- Posts: 61
- Joined: Wed Jul 06, 2005 7:14 am
- Location: Den Haag, the Netherlands
Multiple select queries
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?
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?
- jurriemcflurrie
- Forum Commoner
- Posts: 61
- Joined: Wed Jul 06, 2005 7:14 am
- Location: Den Haag, the Netherlands
- jurriemcflurrie
- Forum Commoner
- Posts: 61
- Joined: Wed Jul 06, 2005 7:14 am
- Location: Den Haag, the Netherlands
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
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
- jurriemcflurrie
- Forum Commoner
- Posts: 61
- Joined: Wed Jul 06, 2005 7:14 am
- Location: Den Haag, the Netherlands
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.
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.
Last edited by jurriemcflurrie on Thu Sep 29, 2005 4:41 am, edited 2 times in total.
- jurriemcflurrie
- Forum Commoner
- Posts: 61
- Joined: Wed Jul 06, 2005 7:14 am
- Location: Den Haag, the Netherlands
In addition to my previous post the way I do it these days is as follows:
Is this a good way or should I change something?
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);
}
?>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());
}
?>- jurriemcflurrie
- Forum Commoner
- Posts: 61
- Joined: Wed Jul 06, 2005 7:14 am
- Location: Den Haag, the Netherlands
- jurriemcflurrie
- Forum Commoner
- Posts: 61
- Joined: Wed Jul 06, 2005 7:14 am
- Location: Den Haag, the Netherlands
if data submitted already exists, mysql_affected_rows() will return 0 also
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;
}
};
?>