Multiple select queries

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Multiple select queries

Post 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?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

It is possible, and is very common.

Just keep the connection open and it'll be speedy :)
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post by jurriemcflurrie »

Cool! It remains open till mysql_close right?

Thanks again!
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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 :)
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post 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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post 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.
Last edited by jurriemcflurrie on Thu Sep 29, 2005 4:41 am, edited 2 times in total.
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post 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?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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()); 

}
?>
:)
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post by jurriemcflurrie »

8O Thnx! Never came up in mind that way
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post 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 :)
Post Reply