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
8O 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 :)