Problem recalling from MySQL db

[neo926]

I have a couple questions, my primary question is regarding my attempt to recall and load data already in a MySQL database into a form I have on a webpage.

So, this is for a sports column site. I have fields in the table in the database for a unique column id, author's name, sport being written about, the title of the column, the date it was submitted, and the actual body of the column. Here is the code that I used to display the form and load the record from the database:

[code]if ($id) {

$sql = "SELECT * FROM columns WHERE id=$id";

$result = mysql_query($sql);

$myrow = mysql_fetch_array($result);

$sport = $myrow["sport"];

$title = $myrow["title"];

$content = $myrow["content"];

?>

<input type=hidden name="id" value="<?php echo $id ?>">

Category:
<form method="post" action="<?php echo $PHP_SELF ?>">

<select name="sport">
<option value="<?php echo $sport ?>" selected><?php echo $sport ?>
<option value="NFL">NFL
<option value="NBA">NBA
<option value="MLB">MLB
<option value="NHL">NHL
<option value="Soccer">Soccer
<option value="Boxing-MMA">Boxing-MMA
<option value="NCAA Football">NCAA Football
<option value="NCAA Basketball">NCAA Basketball
</select>
<br>
Column Title:
<br>
<input type="Text" name="title" value="<?php echo $title ?>" size=65><br>
Column:
<br>
<textarea name="content" rows=30 cols=50 wrap="soft" value="<?php echo $content ?>"></textarea><br>
<br>
<input type="Submit" name="submit" value="Post/Edit Column"> <input type="reset" value="Clear Record">

</form>
[/code]

Now, everything displays fine, except for the $content, or the actual body of the column. For whatever reason, only a portion of the column gets displayed in the text area box. It's not even that the text gets cut off at a certain point, it doesn't display the first part of the column, just the end portion of it.

I'm quite perplexed on why this is happening. I mean, if I'm posting a new column using this form, it all gets submitted to my database no problem, it's just when I recall it that I run into this problem. Any help would be appreciated.

My second question is regarding displaying a list of records pulled from a database.

So, the mainpage of this site has a list of the columns that are currently posted using the following code:

[code]    if (!$id) {

$result = mysql_query("SELECT *,DATE_FORMAT(date,'%M %e, %Y, %l:%i %p') AS mydate FROM columns ORDER BY date DESC",$dbh);

while ($myrow = mysql_fetch_array($result)) {

printf("<table class=transparent cellpadding=0 cellspacing=2>
        <tr>
            <td><img src=\"images/%s.gif\"></td>
            <td><font class=tiny color=#707070><b>%s</font></b><br>
            <a href=\"%s?id=%s\">%s</a><font class=small> by %s</font></td>
        </tr>
        <tr>
            <td></td>
        </tr>
    </table>"
        , $myrow["sport"], $myrow["mydate"], $PHP_SELF, $myrow["id"], $myrow["title"], $myrow["author"]);

    }

}
[/code]

Now, at the moment, since the site isn't up yet, there aren't any columns to post, so when this code is processed, I get this fun little error displayed:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/*****/public_html/sportsoratory/index.php on line 56[/quote]

Now, I get why this error is displayed. My question is, how can I have it so that in the event that there isn't any data to pull when it goes to query the database, I can have an alternate message pop saying "No data was found" or something like that?

Thanks in advance.

[fenway]

Well, quite a lot to cover here.

1. You should really be in the habit of always quoting your values in your SELECT statements, or nasty SQL injection attacks are possible:

[code]$sql = "SELECT * FROM columns WHERE id='$id'";[/code]

2. Your current SELECT dropdown doubles the selected option -- you could always simply check, in-line, if the DB value matches the current value.

3. TEXTAREA tags don't have value attributes -- put the default value (e.g. the retrieved value) in the actual tag itself:

[code]<textarea name="content" rows=30 cols=50 wrap="soft"><?php echo $content ?></textarea>[/code]

4. Assuming your query doesn't fail -- which you're also not handling -- you should check the value of mysql_num_rows($result). If it's non-zero, go ahead with the while loop; otherwise, die gracefully (e.g. "no rows found" message).

Hope I covered everything.

[neo926]

Thanks for the help so far, fenway.

1. I'll do that, I wasn't aware that was bad. Thanks for the heads up.

2. Yeah, I should do that.

3. Thanks. Kind of obvious now that you mention it.

4. Okay, so I altered my code accordingly:

[code]                if (!$id) {

                $result = mysql_query("SELECT *,DATE_FORMAT(date,'%M %e, %Y, %l:%i %p') AS mydate FROM sportatory ORDER BY date DESC",$dbh);

                    if (mysql_num_rows($result)) {

                        while ($myrow = mysql_fetch_array($result)) {

                            printf("<table class=transparent cellpadding=0 cellspacing=2>
                                <tr>
                                    <td><img src=\"images/%s.gif\"></td>
                                    <td><font class=tiny color=#707070><b>%s</font></b><br>
                                    <a href=\"%s?id=%s\">%s</a><font class=small> by %s</font></td>
                                </tr>
                                <tr>
                                    <td></td>
                                </tr>
                            </table>"

                            , $myrow["sport"], $myrow["mydate"], $PHP_SELF, $myrow["id"], $myrow["title"], $myrow["author"]);

                        }

                    } else {

                        echo 'There are no columns to display currently.  Please try back again later.';

                    }


                }
[/code]

and now I get

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/******/public_html/sportsoratory/staff/index.php on line 167[/quote]

I'm assuming I'm still missing a piece of the puzzle?

[fenway]

Sounds like a query error (as I mentioned in #4) -- try adding "or mysql_error($result)" after your query attempt, and see what the server has to say.

[neo926]

Thanks fenway. I ended up getting it figured out.

[fenway]

Glad you got it working -- can you post your solution in case anyone else runs into a similar problem?

[neo926]

Well, I actually had two problems. The first was, in fact, a query error. The other problem was due to the fact that the table I was trying to access was named 'columns', which I learned is a flagged word, and I had to put it in ' ' for the code to process properly.