Split one MySql table in more tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Coldman
Forum Newbie
Posts: 15
Joined: Thu Sep 06, 2007 10:15 am

Split one MySql table in more tables

Post by Coldman »

Hello ther i am trying to fix an MySql query problem but i can not fix this for some days, any help will be welcome.
The problem is this way.
I have an query that selects data from three tables and when i display them they are shown all in one table. All i want to do is to split this table in more tables based on the rows that have similar data.
this is my query:
$query1=mysql_query("SELECT tbltasks.taskid, tbltasks.campid, tbltasks.taskname, tbltasks.taskdate, tblemployee.Name, tblemployee.Surename, tblemployee.profession, tblemployee.foto, tblemployee.drivinglicense, tblemployee.bexh, tblemployee.medicalscreening, tblcamps.campname
FROM tbltasks, tblemployee, tblcamps
WHERE tbltasks.taskid = tblemployee.taskid
AND tbltasks.campid = tblcamps.campid ");

so i want to have different tables based on the tbltasks.taskid, every rows that have similar tbltasks.taskid to be shown in one table and sow on.

Please help?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Split one MySql table in more tables

Post by califdon »

Coldman wrote:Hello ther i am trying to fix an MySql query problem but i can not fix this for some days, any help will be welcome.
The problem is this way.
I have an query that selects data from three tables and when i display them they are shown all in one table. All i want to do is to split this table in more tables based on the rows that have similar data.
this is my query:
$query1=mysql_query("SELECT tbltasks.taskid, tbltasks.campid, tbltasks.taskname, tbltasks.taskdate, tblemployee.Name, tblemployee.Surename, tblemployee.profession, tblemployee.foto, tblemployee.drivinglicense, tblemployee.bexh, tblemployee.medicalscreening, tblcamps.campname
FROM tbltasks, tblemployee, tblcamps
WHERE tbltasks.taskid = tblemployee.taskid
AND tbltasks.campid = tblcamps.campid ");

so i want to have different tables based on the tbltasks.taskid, every rows that have similar tbltasks.taskid to be shown in one table and sow on.

Please help?
I think you are asking how to display data in separate HTML tables. This is an HTML and PHP question, not really a database question, although you will also have to modify your query to get the results in the order you need.

So first you need to add an ORDER BY clause to your query to have the data sorted by taskid.

Then in your PHP that generates the HTML to be sent to the browser, you test each record to see if it is still in the same taskid as the previous record. If it is, you generate another <tr> element in the current table. If the taskid is different from the previous record, you know that you must end the current table and start a new table. The code will look somewhat like this:

Code: Select all

$sql="SELECT  tbltasks.taskid, tbltasks.campid, ... etc.
   FROM tbltasks, tblemployee, tblcamps
   WHERE tbltasks.taskid = tblemployee.taskid
   AND tbltasks.campid = tblcamps.campid
   ORDER BY tbltasks.taskid";
$result=mysql_query($sql) or die(mysql_error());
$last_taskid="XXX";  // arbitrary beginning value for $last_taskid
while ($row=mysql_fetch_assoc($result)) {
   extract($row);
   if ($last_taskid != $taskid) {  // different taskid, so end table and begin new one:
      if ($last_taskid != "XXX") {  // echo end of table only if this is not the first record:
         echo "</table>";
      }
      echo "<table><tr><td colspan='99' align='center'>$taskid</td></tr>";  // begin new table
   }
   echo "<tr><td>$campid</td><td>$taskname</td><td>... etc. ...</td></tr>";  // echo one row of table
}
echo "</table>";
}
Post Reply