Formatting and Query question

[Akshen]

Ok, so I really have 2 questions and was wondering if anyone could shed some light one them. Firstly... I have table like this:

 

|id|name|date|activtiy|

|1|Jo Bloggs|1238411497|went to the Circus|

|2|Fred Kent|1238411497|went to School|

|3|Jane Bloggs|1238411596|went Sailing|

|4|Mike Smith|1238411596|broke his leg|

 

And I want to format my results like this:

 

1238411497

Jo Bloggs went to the Circus.

Fred Kent went to School.

1238411596

Jane Bloggs went Sailing.

Mike Smith broke his leg.

 

I've looked into foreach loops and in all honesty, the confuse me a little and I wonder if they are event appropriate (I assumed by the name that they would be). What would be the best way to go about doing this?

 

Second question would is; I have a table like this:

 

|name|pie|cake|bread|eggs|butter|cheese|pancakes|

|Jo Bloggs|0|1|0|0|0|1|0|

|Mike Smith|1|1|1|1|0|1|0|

 

And I'd like to create a list which echos the things a person likes... so:

 

Jo Bloggs

|cake(dropdown)|

|eggs|

 

Mike Smith

|pie(dropdown)|

|cake|

|bread|

|eggs|

|cheese|

 

Again, what would be the best way to do this? I don't really know where to start on thsi one. I know you can select column names, but how do you select column names based on their data being 1 or 0. Any help greatly appreciated.

[ranjuvs]

Let the first table be table1 and second be table2

 

Let us suppose that we have the result rows(fetched from db) of table1 in rows1 and table2 in rows2 (as an object)

 

solution for question 1

 

foreach($rows1 as $row1) {
    echo "<b>$row->date</b><br />";
    echo $row->name. ' ' . $row->activity."<br />";
}

 

solution for question 2

 

foreach($rows2 as $row2) {
    echo $row2->name."<br />";
    echo "<select name='likes'>";
           if($row2->pie){
               echo "<option value='$row2->pie'>$row2->pie</option>";
           }
           if($row2->cake){
               echo "<option value='$row2->cake'>$row2->cake</option>";
           }
           if($row2->bread){
               echo "<option value='$row2->bread'>$row2->bread</option>";
           }
           if($row2->eggs){
               echo "<option value='$row2->eggs'>$row2->eggs</option>";
           }
           if($row2->butter){
               echo "<option value='$row2->butter'>$row2->butter</option>";
           }
           if($row2->cheese){
               echo "<option value='$row2->cheese'>$row2->cheese</option>";
           }
           if($row2->pancakes){
               echo "<option value='$row2->pancakes'>$row2->pancakes</option>";
           }
    echo "</select><br />";

}

 

Kindly test it(as I haven't tested the code) and get back to me if you are having any issues.

 

Regards

Ranju

[Akshen]

Thanks for the quick response ranjuvs, that's awesome. Ok so I tried the following...

 

<?php $result = mysql_query("SELECT * FROM example_activites");
while ($rows = mysql_fetch_row($result)){
foreach ($rows as $row) {
print $row->date;
}
}
?>

 

The query didn't return anything. I'm guessing the -> (what's it called?) requests column name from the array/query stated?

[ranjuvs]

Use mysql_fetch_object.

 

In your code change the part

 

$rows = mysql_fetch_row($result)

 

to

 

$rows = mysql_fetch_object($result)

 

[Akshen]

Still no joy, although this does work...

 

<?php 
$result = mysql_query("SELECT * FROM example_table");
while ($rows = mysql_fetch_object($result)){
echo $rows->date;
}

 

Seems to be a problem with the foreach?

[lonewolf217]

continue with the method you use here and output the results as ranjuvs mentioned.  He was just going on the assumption you were outputting the data from an array instead of from a database

[Akshen]

<?php 
$result = mysql_query("SELECT * FROM example_table");
while ($rows = mysql_fetch_object($result)){
echo $rows->date;
}

 

Considering the above does work... How come:

 

<?php 
$result = mysql_query("SELECT * FROM example_table");
while ($rows = mysql_fetch_object($result)){
foreach ($rows as $row){
echo $row->date;
}
}

 

Doesn't work? I just get nothing back as soon as I use the foreach statement.

[Akshen]

Ok, knowing barely anything about OOP before this post i've tried to do a bit of homework and it seems like I need to shove all my data into an array first? I've tried to find an example of tutorial on how to do this, or atleast a way to use this foreach loop but can't seem to find anything as I probably still dont know what I'm looking for.

 

Is it even possible to use -> operators from mysql_fetch_object?

[premiso]

In the OOP context, it should be:

 

<?php 
$result = mysql_query("SELECT * FROM example_table");
while ($rows = mysql_fetch_object($result)){
    foreach ($rows as $filedName => $row){
        echo "Field: {$fieldName} returned {$row}";
    }
}
?>

 

A foreach on an object will iterate through the viewable properties and return its name as the index, in this case the MySQL column name and the value of the field as a value. This is not used very much because generally you know how you want to display the data, like you wanted to display the date.

 

Instead most people skip the foreach portion and just do something like this:

<?php 
$result = mysql_query("SELECT * FROM example_table");
while ($rows = mysql_fetch_object($result)){
    echo "Date: " . $rows->date . "<br />";
    echo "Name: " . $rows->name . "<br />"; // assuming you have name column in the table of the db.
}
?>

 

What, I believe you are assuming is that the fetch_object returns the full array of objects. This is not true, that is why you have to while loop it. Here is an example, of what I think you are thinking it should produce:

<?php 
$result = mysql_query("SELECT * FROM example_table");
$rows = array();
while ($row = mysql_fetch_object($result)){
    $rows[] = $row;
}

foreach ($rows as $row) {
    echo "Date: " . $row->date . "<br />";
    echo "Name: " . $row->name . "<br />"; // assuming you have name column in the table of the db.
}
?>

 

Notice the assignment to the array $rows, that loops through all the rows and assigns each one to the $rows array, then we foreach loop through that doing the displaying. Hopefully that clears this up for you.