matt.sisto Posted May 5, 2009 Share Posted May 5, 2009 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. <?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> Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/ Share on other sites More sharing options...
Zhadus Posted May 5, 2009 Share Posted May 5, 2009 You'll want to do some research with the MySQL "JOIN" or perhaps "LEFT JOIN" http://dev.mysql.com/doc/refman/5.0/en/join.html Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826854 Share on other sites More sharing options...
Mchl Posted May 5, 2009 Share Posted May 5, 2009 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 = ? Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826857 Share on other sites More sharing options...
Ken2k7 Posted May 5, 2009 Share Posted May 5, 2009 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. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826860 Share on other sites More sharing options...
Mchl Posted May 5, 2009 Share Posted May 5, 2009 In general using SELECT * should be avoided if possible. Maybe not avoided... just used with care. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826868 Share on other sites More sharing options...
Ken2k7 Posted May 5, 2009 Share Posted May 5, 2009 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. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826871 Share on other sites More sharing options...
matt.sisto Posted May 5, 2009 Author Share Posted May 5, 2009 If I selected everything but client_id from table2, would this make a difference? SELECT * FROM table2 INNER JOIN client ON (table2.client_id = client.client_id) WHERE org_id = ? Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826878 Share on other sites More sharing options...
Mchl Posted May 5, 2009 Share Posted May 5, 2009 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. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826886 Share on other sites More sharing options...
matt.sisto Posted May 5, 2009 Author Share Posted May 5, 2009 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. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826890 Share on other sites More sharing options...
Ken2k7 Posted May 5, 2009 Share Posted May 5, 2009 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. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826893 Share on other sites More sharing options...
Mchl Posted May 5, 2009 Share Posted May 5, 2009 UNION will stack results from one query under another. Not exactly what you want. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826896 Share on other sites More sharing options...
matt.sisto Posted May 5, 2009 Author Share Posted May 5, 2009 Is it possible to use each value in the array as a new variable? Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826928 Share on other sites More sharing options...
Ken2k7 Posted May 5, 2009 Share Posted May 5, 2009 Is it possible to use each value in the array as a new variable? Yes it is. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826944 Share on other sites More sharing options...
matt.sisto Posted May 5, 2009 Author Share Posted May 5, 2009 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. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826954 Share on other sites More sharing options...
Mchl Posted May 5, 2009 Share Posted May 5, 2009 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. Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-826986 Share on other sites More sharing options...
matt.sisto Posted May 6, 2009 Author Share Posted May 6, 2009 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' Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-827376 Share on other sites More sharing options...
matt.sisto Posted May 6, 2009 Author Share Posted May 6, 2009 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' Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-827394 Share on other sites More sharing options...
Mchl Posted May 6, 2009 Share Posted May 6, 2009 How about WHERE MONTH(calendar_events.event_start) = 6 Link to comment https://forums.phpfreaks.com/topic/156971-solved-using-an-array-in-an-sql-select-statement/#findComment-827434 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.