Join 137,429 PHP Programmers for FREE! Get instant access to thousands of PHP experts, tutorials, code snippets, and more! There are 1,901 people online right now. Registration is fast and FREE... Join Now!
I have a table I get from a few class methods that query a mysql database, i'm trying to keep all my SQL inside that class, and no where else. Now my conflict is that I can use array_multisort to sort that table and order it much like you would do with an sql statement. However, I think that having alot of records all in one 2d array might be very inefficent, even too inefficent. What i am trying to do is to have that table show on a few pages if needed and to be sorted according to what the user chooses (much like what you can do here with the forums).
My question is, should I be using SQL to achive that and thereby hurt my code simplicity and modularity or use a 2d array sort and extract the data from there?
/** * Get a full list of all polls. * * @param CPollSQL $database The database resource. * @param String order_by The column name which the results will be sorted by. * @param int $order_type The type of ordering -- SORT_ASC or SORT_DESC * @param int $start The start point from where in the database the results will be returned. * @param int $limit The limit of how many record will be returned. For unlimited number records use 0(default). * @param int &$total This variable will be used to return the total number of polls. * @return mixed[][] 2D array, assosiative -- poll_id, question, votes -- rows. */ public static function GetAllPollsFullList(CPollSQL $database, $order_by = 'poll_id', $order_type = SORT_ASC, $start = 0, $limit = 0, &$total) { global $tblPolls, $tblResults;
// Get the total number of records $query = 'SELECT COUNT(poll_id) AS total_polls FROM ' . $tblPolls . ' LIMIT 0, 1'; $database->RunQuery($query); $total = $database->GetResults(0, 'total_polls');
// Get the actual table of values $query = ' SELECT a.poll_id, a.question, SUM(b.result) AS votes' . ' FROM ' . $tblPolls . ' a, ' . $tblResults . ' b' . ' WHERE a.poll_id = b.poll_id' . ' GROUP BY a.poll_id, b.poll_id ' . ' ORDER BY ' . $order_by . ' ' . $sql_order_type . ' ' . $sql_limit; $database->RunQuery($query); return $database->GetResults(); }
Now my only issue is with the SQL code, the second query. It works fine, but I want the number of votes to be equal to zero if there are no records in result with the same poll_id. In other words, if there is a poll_id in the polls table but no matching poll_id/s in the results table. How do I do that with SQL?
This is such a different way of coding than me! (i don't use OOP yet) anyway, it wouldn't return anything if there we're no votes... I think there may be a simpler SQL statement for this, but with a bit more php maybe.
I'm not sure what way your database library returns the results, i'll assume just in an array so...
CODE
// get the list of polls $query = ' SELECT * FROM a ORDER BY ' .$order_by. ' ' .$sql_order_type. ';';
// count votes in b for corresponding poll in a for($i = 0; $i < count($polls); $i++){ // or use a foreach loop, which ever be your preference! $query = 'SELECT COUNT(*) FROM b WHERE `poll_id` = "' .$polls[$i]['poll_id']. '"; $database->RunQuery($query);
// assign results to array item with index name of poll id from a $poll_results[$polls[$i]['poll_id']] = $database->GetResults(); }
return $polls, $poll_results;
so in your main file you have
CODE
$polls, $poll_results = GetAllPollsFullList();
$polls[<int>]['question'] // contains the poll question $polls[<int>]['poll_id'] // contains the poll id from a $poll_results[$polls[<int>]['poll_id']]['COUNT(*)'] // contains the poll result ( i think!)
you may also just use the $i variable in the first part to create the index in the $poll_results array rather than $polls[$i]['poll_id'] Though the way i did it is a bit more complicated, it means the results are kept with their respective poll_id from `a`
I threw the commentary in the code. I believe this is what you mean.
CODE
// Get the total number of records // no need to limit here, only one value will be returned $query = 'SELECT COUNT(poll_id) AS total_polls FROM ' . $tblPolls; $database->RunQuery($query); $total = $database->GetResults(0, 'total_polls');
// Get the actual table of values // in mysql, the IFNULL function is used to replace a null result // with a non null return value
// This is an outer join // what this means is that all the records from a are joined to b // however, if an a record exists that's no in b, just the a record // is included, all b fields will be null $query = 'SELECT a.poll_id, a.question, IFNULL(SUM(b.result),0) AS votes' . . ' FROM ' . $tblPolls . ' a' . ' LEFT OUTER JOIN ' . $tblResults . ' b' . ' ON a.poll_id = b.poll_id' . ' GROUP BY a.poll_id' . . ' ORDER BY a.' . $order_by . ' ' . $sql_order_type . ' ' . $sql_limit; $database->RunQuery($query); return $database->GetResults();
I threw the commentary in the code. I believe this is what you mean.
CODE
// Get the total number of records // no need to limit here, only one value will be returned $query = 'SELECT COUNT(poll_id) AS total_polls FROM ' . $tblPolls; $database->RunQuery($query); $total = $database->GetResults(0, 'total_polls');
// Get the actual table of values // in mysql, the IFNULL function is used to replace a null result // with a non null return value
// This is an outer join // what this means is that all the records from a are joined to b // however, if an a record exists that's no in b, just the a record // is included, all b fields will be null $query = 'SELECT a.poll_id, a.question, IFNULL(SUM(b.result),0) AS votes' . . ' FROM ' . $tblPolls . ' a' . ' LEFT OUTER JOIN ' . $tblResults . ' b' . ' ON a.poll_id = b.poll_id' . ' GROUP BY a.poll_id' . . ' ORDER BY a.' . $order_by . ' ' . $sql_order_type . ' ' . $sql_limit; $database->RunQuery($query); return $database->GetResults();
Hope this helps.
Right on the money! Thanks alot, that's exactly what i meant and it works like a charm.
I was trying to look into it, and maybe try subquery and do some conditioning, turned out pretty ugly. Thanks again for saving me some trouble.
Just one thing about the code, about the "LIMIT 0, 1" you deleted from the code. I know it would only return 1 record, i just saw something similar used by the phpbb guys, thought i should give it a shoot, check it an extra time with the limit just to be sure. But I was mistaken. Anyway should i do this check else where? When for example searching for a value in the database?
gm04030276 Thanks for the code and for your time, but it was exactly what i was trying to avoid, well at least part of what i was trying to avoid when i set out to do this all in SQL. I do have a method that does that already, counts the number of votes for a given poll object. That is infact what i was doing before, something very similar just with OOP. I think i started a topic about that here a while ago, about OOP and seprating code from design, helped me alot with my project reading all the information in one of the links there. Here it is: http://www.dreamincode.net/forums/index.ph...c=32521&hl= might help you get the "yet" out of the "i don't use OOP yet".
This post has been edited by Mike007: 6 Jan, 2008 - 05:56 PM
Just one thing about the code, about the "LIMIT 0, 1" you deleted from the code. I know it would only return 1 record, i just saw something similar used by the phpbb guys, thought i should give it a shoot, check it an extra time with the limit just to be sure. But I was mistaken. Anyway should i do this check else where? When for example searching for a value in the database?
My fear is when the limit is applied. If it's after the result set, which I believe is the case, it simply does nothing. However, if it somehow apply prior to the count, you might get junk. Seemed safest to chuck it.
Anytime you have only aggregate functions in a query, e.g. sum, count, min, max, etc, then you'll always have one and only one row as a result. If it's a group by, there is a chance of get zero rows returned.
For database searching... a limit is your friend for something like paging. However, if you're selecting from a single table using that table's primary key, you'll get either zero or one rows back, nothing more. Some folks also use distinct when it not required, it's a sign they don't trust their logic or their database.
Now, interesting one for you, today, i was trying to do something in Access for my assignment for college and was struggling to think of the query for what i was trying to do, and then remembered yours...coincidentially its exactly what i want to do today, go figure! however, it doesn't work for me
table tbl_Game with fields `gameID` `title` `age` `year` `gmanufacturer` table tbl_Booking with fields `reference` `date` `start_time` `stop_time` `username` `computerID` `gameID`
i tried using the following modifed version of baavgai's code to select all the game titles and count how many times each one has been booked so as to get the popularity of each game by how many times it has been booked to be played (NB, database is for a computer gaming center)
CODE
SELECT tbl_Game.gameID, tbl_Game.title, SUM(tbl_Booking.gameID) AS Popular FROM tbl_Game LEFT JOIN tbl_Booking ON tbl_Game.gameID = tbl_Booking.gameID GROUP BY tbl_Game.gameID ORDER BY tbl_Game.gameID;
Access didn't like your IFNULL() function so i had to remove that but this code give me error "You tried to execute a query that does not include the specified expression 'title' as part of an aggregate function" any suggestions!?
This post has been edited by gm04030276: 8 Jan, 2008 - 09:03 AM
Ok first of all why are you suming the id numbers together? it should be COUNT not SUM. Second a quick google seach showed as i suspected already, that MS Access does not have IFNULL function, that is a mysql built-in function. Instead they have the ISNULL function that returns true or false, so you will probably have to use a condition there to make it work, either when case or the IIF function, not exactly sure, but there is no shame in trying them all . And about the error you are reciving, what it says is that basicly you have a function in this case SUM because it is the only one i see in your SQL, that was not given the right paramater or something like that, not sure why it gives you that to be honost, try changing it to count maybe it will work after all.
Good luck with the project anyway, sounds interesting. Reminds me of my highschool project, kinda made it huge (got a 100 on it of course). It was in 2006, the good days when pimp my ride was still watchable, ok it was GREAT! With xzibit and the old crew. Anyway it was a garage, was fun, really fun after it was over, made alot of drafts and tries, before I could actually make a good Access database.
I was just using your code in hope that it might do something along the lines of what i wanted! after alot of rewriting and googling what M$ say in their help thing for the error, i finally figured out what the code was actually doing and what i needed to do to make it work! The error was because i was trying to use the tbl_Game.title field without having used it anywhere else so this is the code i finally got to do what i want!
CODE
SELECT Sum([tbl_Booking].[gameID]) AS Total, tbl_Game.title FROM tbl_Game LEFT JOIN tbl_Booking ON tbl_Game.gameID = tbl_Booking.gameID GROUP BY tbl_Game.title ORDER BY Sum([tbl_Booking].[gameID]);
I use the gameID to link to because i have to, its the foreign key in the booking table but don't show it, group by the title (which should probably technically be the primary key because it is unique anyway! And would probably have made this work alot sooner! but its fun to learn! SQL is something my eyes are quickly being opened to the power of!!)
| 