Jump to content

[SOLVED] Using an array in an SQL SELECT statement


matt.sisto

Recommended Posts

Hi all, I have written a script that selects all the client_id's that a represented by user defined organisation. Because there are multiple values I have identified client_id as an array, but I then want to use these values to select all the bookings with those values in a different table, but I don't know how to extract the values in my array to use in a new SQL statement, at the moment I am just echoing out the values. Any and all help appreciated.  8)

<?php
session_start();

  if (!isset($_SESSION['username']))
    {
   header("Location: login.php");
   exit();
  }
  
  require "dbconn2.php"; 
  
  $org_id = $_POST["org_id"]; 
  $month1 = $_POST["month1"];
  $day1 = $_POST["day1"];
  $year1 = $_POST["year1"];
  $month2 = $_POST["month2"];
  $day2 = $_POST["day2"];
  $year2 = $_POST["year2"];

  $invoice_start = $year1."-".$month1."-".$day1." ".$_POST["event_start"];
  $invoice_end = $year2."-".$month2."-".$day2." ".$_POST["event_end"];

if ($org_id !=null){
  $sql = "SELECT client_id FROM client WHERE org_id='$org_id'";
  $result = mysql_query ($sql, $connection)
    or die ("Couldn't perform query $sql <br />".mysql_error());
while($row = mysql_fetch_array($result)){
$arrayClient = array($row);
echo $row['client_id'];
echo "<br />";
  }
}

?>
<html>
<head>
<title>Invoice</title>
</head>
<body>
</body>
</html>

 

It would be faster to do it using joins

 

SELECT * FROM table2 INNER JOIN client ON (table2.client_id = client.client_id) WHERE org_id = ?

That SQL will cause a problem even though it's an example. The clash of two columns with the same name is never cool.

In general using SELECT * should be avoided if possible. Maybe not avoided... just used with care.

SELECT * on one table is perfectly fine because MySQL won't throw up and yell at you, but I agree that it should be used with care. I see a lot of people selecting a huge bunch of columns when they only need 4 of them.

SELECT * on one table is perfectly fine because MySQL won't throw up and yell at you, but I agree that it should be used with care. I see a lot of people selecting a huge bunch of columns when they only need 4 of them.

 

It won't throw up and yell when you use it in joined tables either (i.e. it will not throw an error). You might however have some problems when retrieving data from resultset.

 

matt.sisto: the column on which join is performed will only be present once in resultset anyways. No worry about that. It would be better however if you just explicitly put in the query only these columns that you need.

I am just reading about UNION, what if I did something like this:

 

SELECT client_id FROM client WHERE org_id='$org_id' UNION ALL SELECT event_id, event_start, event_end, fee FROM calendar_events WHERE client_id='$client_id' 

 

But the client_id used in the second query would be multiple results from the first query, if that makes sense.

I am just reading about UNION, what if I did something like this:

 

SELECT client_id FROM client WHERE org_id='$org_id' UNION ALL SELECT event_id, event_start, event_end, fee FROM calendar_events WHERE client_id='$client_id' 

 

But the client_id used in the second query would be multiple results from the first query, if that makes sense.

No. MySQL will definitely throw up and yell at you there. You can only use UNION if you're selecting the same number of columns from the tables you're using UNION on. UNION ALL rule is the same.

SELECT event_id, event_start, event_end, fee FROM calendar_events INNER JOIN client ON (calendar_events.client_id = client.client_id) WHERE org_id = '$org_id'

 

So this seems to be the best option at present, I will do some reading and test it out, thanks everyone for all of your the contributions.

Actually you can make it shorter this way

 

SELECT event_id, event_start, event_end, fee FROM calendar_events CROSS JOIN client USING (client_id) WHERE org_id = '$org_id'

 

you can use 'USING' because the column has same name in both tables.

 

I will still need to further define my selection by dates, as I intend to create a monthly invoice. I will test it and let you know how I get on. Thanks again.

 

SELECT event_id, event_start, event_end, fee FROM calendar_events WHERE event_start = '$invoice_start' AND event_end = '$invoice_end' CROSS JOIN client USING (client_id) WHERE org_id = '$org_id'

 

Sorted thanks for all the help everyone.

 

SELECT event_id, event_start, event_end, fee
FROM calendar_events
CROSS JOIN client
USING (client_id)
WHERE calendar_events.event_start >= '2009-06-01'
AND calendar_events.event_end <= '2009-06-30'
AND client.org_id = '10'

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.