MySQL PHP Recordsets

[warstormer]

As this is to do with MySQL and PHP, not sure where to post it... Basically it's about manipulating recordsets, and I'm coming from an ASP background so bear with me.

 

I have this: $result = mysql_query("select * from news where news_alert = 'A' LIMIT 0, 30 ") or die(mysql_error());

 

Now I assume that $result is my recordset. How do I then display individual data? In ASP you would just write '=rs("field_name")' and there the data would appear...

 

I am actually trying to pull out the name of a relevant image and insert it into a line of html like so:

 

<img src="images/<?recordset data ?>" />

 

Any help much appreciated!

[wildteen88]

To pull data from a recor set you'd use mysql_fetch_assoc or mysql_fetch_array. I use assoc as I prefer to work with associative arrays ($arr['key']]) rather than indices ($arr[0]).

 

If your query returns multiple rows then you'd use mysql_fetch_assoc within a while loop to loop through the rows that was returned. If your query only returns 1 row then you wont need to use a while loop.

 

Here's an example:

<?php

// connect/select database

$result = mysql_query("select * from news where news_alert = 'A' LIMIT 0, 30 ") or die(mysql_error());

// check that the query returned any results
if(mysql_num_rows($result) > 0)
{
    // loop through the results
    while($row = mysql_fetch_assoc($result))
    {
        echo '<img src="images/' . $row['field_name_here'] . '" />';
    }
}

 

change field_name_here with the actual field (or column) name from your table.

[warstormer]

Cool, thanks very helpful.... if I did just have 1 row in my recordset what's the syntax for displaying the data? Is it just...

 

{

  $row = mysql_fetch_assoc($result)

    {

        echo '<img src="images/' . $row['field_name_here'] . '" />';

    }

}

 

sorry if that's completely wrong!

[Illusion]

If u r sure that it returns only one record, it works. However it is always better to put in a while loop.

[warstormer]

Ok thanks... I'll take your advice...

[GingerRobot]

I completely disagree. Why have a while loop when you dont need one? When you come back to look at that, it would be very easy to get confused. You'll be expecting that code block to repeat and it doesn't.

 

Cool, thanks very helpful.... if I did just have 1 row in my recordset what's the syntax for displaying the data? Is it just...

 

{

  $row = mysql_fetch_assoc($result)

    {

        echo '<img src="images/' . $row['field_name_here'] . '" />';

    }

}

 

sorry if that's completely wrong!

 

Yeah, thats right. Although the braces are unnecessary.

 

Personally, if i'm retrieving just one field from a single row, i tend to use the mysql_result() function - seems to make your intentions clearer that way. However, thats just a matter of opinion.

[warstormer]

Thanks for that as well... appreciate all the advice....! Guess I'll see which one works best...

 

I completely disagree. Why have a while loop when you dont need one? When you come back to look at that, it would be very easy to get confused. You'll be expecting that code block to repeat and it doesn't.

 

Cool, thanks very helpful.... if I did just have 1 row in my recordset what's the syntax for displaying the data? Is it just...

 

{

  $row = mysql_fetch_assoc($result)

    {

        echo '<img src="images/' . $row['field_name_here'] . '" />';

    }

}

 

sorry if that's completely wrong!

 

Yeah, thats right. Although the braces are unnecessary.

 

Personally, if i'm retrieving just one field from a single row, i tend to use the mysql_result() function - seems to make your intentions clearer that way. However, thats just a matter of opinion.