How to accomplish this - Nested loops

[marijuana]

Hi,

I am having a small struggle with this nested loop table. I can't seem to get this right. This is what I am doing.

 

I have 4 tables

bus_info: where I store bus model # and name

client_info: name of the clients

jobs: I store all the jobs here. there is ad_id, customer_id, bus_id and side_id

sides: sides and their id number

 

I am able to pull the data from the db with 4 while loops and list them on the browser in excel format. However all the records are coming individually, which I don't what. I have 4 sql statements.

First SQL selects all jobs from the jobs table. >

mysql_query("SELECT * FROM jobs ")

 

Second SQL selects the customer name  >

mysql_query("SELECT * FROM customer_info WHERE customer_id =$customer_name")

Third SQL selects bus info >

mysql_query("select * from bus_info where bus_id =$bus_name ")

and Forth SQL selects sides. >

mysql_query("select * from sides where side_id =$side_name")

 

What I am trying to do is basically group the ads with the corresponding bus. This might be little confusing, I have created a chart that explains what I am actually tying to do. Please take a look

 

Could anyone possibly help me with this? Thank you very much.

 

 

[attachment deleted by admin]

[Psycho]

Well, first off, DON'T do looping queries!!! The whole point of having a relational database is to be able to extract records that are related. Without knowing the exact database layout, this is only a guess as to what your query should look like:

 

SELECT *
FROM jobs
JOIN customer_info ON jobs.customer_id = customer_info.id
JOIN bus_info ON jobs.bus_id = bus_info.id
JOIN sides ON jobs.side_id = sides.id

[marijuana]

mjdamato thank you for the response.

 

I made the query as you suggested but it did not do any difference. It is the same as I did before except it is shorter this time. It pulls the records individually. If you look at the chart I originally inserted, customers does not have to occupy whole cell if all the sides are taken by one customer. I can do that part individually.  Basically, the rest of the chart could look like the first row.  Here is what I am doing.

 

<table>
<?php 
  $result_ads = mysql_query("SELECT * FROM jobs ".
  "JOIN customer_info ON jobs.customer_id = customer_info.customer_id ".
  "JOIN bus_info ON jobs.bus_id = bus_info.bus_id ".
  "JOIN sides ON jobs.side_id=sides.side_id ".
  "ORDER BY bus_info.bus_number ASC") or die(mysql_error());
  
  
while ($row = mysql_fetch_array($result_ads))
{
  ?>

<tr>
    <td><?php echo $row['ad_id'] ; ?></td>
    <td><?php echo $row['bus_number']; ?></td>
    <td><?php echo $row['bus_name']; ?></td>
    <td><?php echo $row['side_name']; ?></td>
    <td><?php echo $row['customer_name']; ?></td>
    <td><?php echo date('m/d/Y', strtotime($row['date_start'])); ?></td>
    <td><?php echo date('m/d/Y', strtotime($row['date_end'])); ?></td>
    <td><?php echo date('m/d/Y', strtotime($row['post_date'])); ?></td>
    <td><?php echo $row['installer']; ?></td>
    <td><input type="checkbox" value="<?php echo $row['ad_status']; ?>" <?php if ($row['ad_status'] == 1) {
echo "checked"; 
} ?> />
    </form>	</td>
    <td><input type="submit" value="Edit" name="edit_record" /><input type="submit" value="Completed" name="edit_record" /></td>
  </tr>
  <?php
}

mysql_close($con);
?>
</table>
{

 

What should I do to make the sides and advertisers grouped with the corresponding bus model? This is the part that confuses me the most. Thanks again for the response.

 

 

[marijuana]

Does anyone have any idea how to do this? or is there any link you guys know that could be helpful to me? Thanks.

[marijuana]

No help?

Could anyone please possibly help me about this? Thank you very much

[Psycho]

To be honest I had started some code, but I had more questions than answers and I really didn't want to invest the time to formulate all the questions. If you want to provide detailed specifics then I might be able to provide more help.

 

Specifically you need to provide details regarding the relationship, if any, between the data. The screenshot you provided is helpful, but the the image doesn't make it obvious what table the data for each column is coming from. For example,the first column is called BUS#. I would think that comes from the jobs table since that is where all the data is related to, but you also have a bus_info table. To make matters worse, your sample code in your second post shows data in a different order than your image and there are more fields than the image.

 

Lastly, I can't tell if there is any relationship between sub-data of the columns. For example, in the image record 805 has three values under sides and three under advertiser. Is the first side value related to the first advertiser value? If so, how is that represented in the database. Also, in record 811, there appear to be three values under the installer column, but they are not separated into three cells as they are for other records. Was that intentional? If so, what is the logic to determine that?

[marijuana]

mjdamato,

Thank you for your response. Sorry for not being clear in my previous posts. I'll explain the parts where you get confused.

 

For example,the first column is called BUS#. I would think that comes from the jobs table since that is where all the data is related to, but you also have a bus_info table. To make matters worse, your sample code in your second post shows data in a different order than your image and there are more fields than the image.

 

BUS # does not come from jobs table. I store all bus information under bus_info table separated into 3 columns . Those columns are: bus_id, bus_name, bus_number. In the jobs table I retrieve those bus information by a sql statement that you provided. Instead of this sql statement you provided I had 4 loops before.

 

For example, in the image record 805 has three values under sides and three under advertiser. Is the first side value related to the first advertiser value? If so, how is that represented in the database.

 

Yes, the sides related to the advertisers. Basically, there are 4 sides of a bus. Advertiser rents a bus' side. An advertiser could rent 2,  3 or all sides of the bus, thats up to them.

This is the part that confuses me the most.  I am not really clear how I am going to represent this in my DB. I am mostly focused on this part. But I can't seem to find any helpful articles.

 

Also, in record 811, there appear to be three values under the installer column, but they are not separated into three cells as they are for other records. Was that intentional? If so, what is the

That chart was done quickly so it was supposed to be different cells. At this point I am not really concerned about the installers.

 

Hopefully this is clear. Thanks again for your time that you spend on this thread.

 

 

 

 

[gizmola]

A couple comments:

 

First save yourself the headache and don't do a bunch of concatenated lines in a query string.  Just one string will do fine:

 

Yours:

$result_ads = mysql_query("SELECT * FROM jobs ".
  "JOIN customer_info ON jobs.customer_id = customer_info.customer_id ".
  "JOIN bus_info ON jobs.bus_id = bus_info.bus_id ".
  "JOIN sides ON jobs.side_id=sides.side_id ".
  "ORDER BY bus_info.bus_number ASC") or die(mysql_error());

 

Should be:

 

$result_ads = mysql_query("SELECT * FROM jobs 
   JOIN customer_info ON jobs.customer_id = customer_info.customer_id
  JOIN bus_info ON jobs.bus_id = bus_info.bus_id
  JOIN sides ON jobs.side_id=sides.side_id
  ORDER BY bus_info.bus_number ASC") or die(mysql_error());

 

Now this shows you how to join up all the tables, which is fine.  Hopefully you understand that you get a row for every time the tables join together, so you'll have multiple rows for a bus, for every side/advertiser you have.  You'll have to use PHP to figure out when one bus starts and the next ends, in making your table.  This basically means you need a variable for bus where you check when the bus changes, and close out your tr and start a new one whenever that occurs.

 

The main issue you have right now is that your query doesn't actually specify any columns in any of the tables other than jobs. 

 

$result_ads = mysql_query("SELECT jobs.*, customer_info.*, bus_info.*, sides.* FROM jobs 
   JOIN customer_info ON jobs.customer_id = customer_info.customer_id
  JOIN bus_info ON jobs.bus_id = bus_info.bus_id
  JOIN sides ON jobs.side_id=sides.side_id
  ORDER BY bus_info.bus_number ASC") or die(mysql_error());

 

This is just the easy way out because what you should really do is just specify the individual columns you need, like bus.name or what ever you need for your output.  Because I don't know what your database schema looks like, I just put tablename.* for every table, but you should only query for the columns you need for your php script.