64025

multiple mysql queries into one php two-dimensional array

Question:

I'm refactoring my code and just hit a snag. The first query in the code below gets the tab_id of the latest submitted tabs. The second Query gets the detail of each tab. In my old way of doing it, i embedded php and html and it was truly an utter mess. Right now I'd like to merge the 2 queries into 1 and/or load it into an array.

feel free to ask and/or butcher

function get_newest_tabs() { $db_open; $sql = "SELECT tab_id, song_id, user_id FROM tabs ORDER BY time_added DESC ". "LIMIT 15"; $result = mysql_query($sql) or die("ERROR - newest tabs function: ".mysql_error()); if (mysql_num_rows($result) > 0) { for($i = 0; $i < mysql_num_rows($result); $i++) { $tab_id = mysql_result($result, $i, "tab_id"); $db_open; $sql = "SELECT tabs.tab_id, tabs.tab_version, tabs.number_of_hits, artist.artist_name, users.user_alias, songs.song_name, tabs.time_added FROM tabs, users, artist, songs WHERE tabs.tab_id ='".$tab_id."' AND tabs.user_id = users.user_id AND tabs.song_id = songs.song_id AND songs.artist_id = artist.artist_id"; $result2 = mysql_query($sql) or die("ERROR - i3p mysql - 4: ".mysql_error()); if(mysql_num_rows($result2) == 1) { $song_name = mysql_result($result2, 0, "songs.song_name"); $artist_name = mysql_result($result2, 0, "artist.artist_name"); $user_alias = mysql_result($result2, 0, "users.user_alias"); $tab_version = mysql_result($result2, 0, "tabs.tab_version"); $number_of_hits = mysql_result($result2, 0, "tabs.number_of_hits"); $time_added = mysql_result($result2, 0, "tabs.time_added"); } } } }

Answer1:

I'd suggest using JOIN instead of selecting from multiple tables. You can also join the tabs table.

SELECT tabs.tab_id, tabs.song_id, tabs.user_id, tabs.tab_version, tabs.number_of_hits, artist.artist_name, users.user_alias, songs.song_name, tabs.time_added FROM tabs LEFT JOIN users ON users.user_id = tabs.user_id LEFT JOIN songs ON songs.song_id = tabs.song_id LEFT JOIN artist ON artist.artist_id = songs.artist_id ORDER BY tabs.time_added DESC LIMIT 15

Then you could do a loop like:

while($row = mysql_fetch_array($result)) { $tabs[$row['tab_id']] = $row; }

As long as you have one user/song/artist per tab that will get you an array of your data.

Answer2:

Why not use sql:

WHERE tabs.tab_id IN (1,2,3,4)

or event

WHERE tabs.tab_id in (select tab_id from tabs ... )

I think it's rather SQL related question.

Answer3:

I'm not sure how to read hegemon's suggestions, but AGREED it's an SQL question. That means you should do all your work in a MySQL interface without PHP in the way, then bring the finished SQL strings in. I use phpMyAdmin, which has a convenient feature that formats the SQL for use in PHP.

Recommend

  • PHP Warning: strpos() expects parameter 1 to be string, array given
  • Vue three columns of checkboxes in Bootstrap 4
  • dynamic variables in preg_replace in PHP
  • Firebase Storage: string does not match format base64: invalid character found. Only when debug is o
  • Using a join with three tables when a field might be null
  • Insert records into two table at once
  • ASP.NET, C# How to Pass a StringQuery to a custom SQL Command
  • Connecting Google Cloud SQL with Wordpress on Google Compute Engine
  • Passing parameter through “window.location.href”
  • SQL Server re-calculate or not?
  • Possible to get the line number of the currently executing sproc in SQL Server?
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • It is possible use the same sql azure instance from two different cloud service of two different sub
  • TSQL Rolling Average of Time Groupings
  • distinct values from multiple fields within one table ORACLE SQL
  • What is corresponding c++ data type to SQL numeric(18,0) data type?
  • How to skip require in ruby?
  • Randomizing -and remembering that randomisation- multiple choice questions in php
  • How can Delete be both a DDL and a DML statement
  • How to 'create temp table as select' in Slick?
  • Add dynamic data to line chart from mysql database with highcharts
  • Using Sax parsing to edit and write XML in VB6
  • pyodbc doesn't report sql server error
  • Limiting recursion to certain level - Duplicate rows
  • Breeze - Deleted Items nav properties bug
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Using $this when not in object context
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • javaw.exe and eclipse startup problems
  • How to limit post in wp_query
  • Display Images one by one with next and previous functionality
  • Delete MySQLi record without showing the id in the URL
  • php design question - will a Helper help here?
  • AngularJs get employee from factory
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Hits per day in Google Big Query
  • need help with bizarre java.net.HttpURLConnection behavior
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4