Page 1 of 2

using specific mysql data

Posted: Sat Apr 11, 2009 7:33 pm
by tomsace
Hi,
I have the following code to show mydql data in my web page:

Code: Select all

<?php
// Connecting, selecting database
$link = mysql_connect('localhost', 'username', 'password')
    or die('Could not connect: ' . mysql_error());
mysql_select_db('tomsace_games') or die('Could not select database');
 
// Performing SQL query
$query = 'SELECT * FROM games';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $col_value) {
        echo "$col_value";
    }
}
 
// Closing connection
mysql_close($link);
?>
This shows all data in the table, how do I show onbe specific piece of data? for example at the minuthe this shows a games title, description, instrucations and date added to the site, how do I show just the title?

Re: using specific mysql data

Posted: Sat Apr 11, 2009 8:00 pm
by McInfo
If one of your fields is named title, you could echo a list of titles with

Code: Select all

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo $line['title']."\n";
}
Or you could store everything in an array and echo a specific title later in your script:

Code: Select all

$rows = array();
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $rows[] = $line;
}
echo $rows[0]['title'];
Edit: This post was recovered from search engine cache.

Re: using specific mysql data

Posted: Sun Apr 12, 2009 5:21 am
by tomsace
Thanks alot, works great!

Now all my games in my database are listed in categories (action, adventure, fighting etc...)
How do I show all games that are listed under 'action' under the 'category' part of my database?

Re: using specific mysql data

Posted: Sun Apr 12, 2009 5:46 am
by requinix
You change your query so it only SELECTs the rows WHERE the category is "action".

Re: using specific mysql data

Posted: Sun Apr 12, 2009 8:43 am
by tomsace
Thanks alot.
One last thing is I was wondering if it is possible to use these 'titles' I am using from my database as an image?
I need to use each title as an image.
For example: <img src="images/TITLE FROM DATABASE.jpg" height="75" width="75">...

Thanks alot.

Re: using specific mysql data

Posted: Mon Apr 13, 2009 8:25 am
by tomsace
Doesn't matter, I have figured it out!
I used the line:
<img src="images/$line[title]" height="75" width="75">...

Re: using specific mysql data

Posted: Mon Apr 13, 2009 11:31 am
by tomsace

Code: Select all

// Performing SQL query
$query = 'SELECT * FROM games';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
        echo "<table cellpadding=0 cellspacing=0 border=0 width=97 align=left><tr><td>
              <table cellpadding=0 cellspacing=0 border=0 width=85 align=center>
              <tr><td><img src=images/redthumbtop.gif></td></tr><tr><td align=center bgcolor=#be1313>";
        echo "<a href=games/$line[idname].php><img src=games/image/$line[idname].jpg border=0></a></td></tr>
              <tr><td align=center bgcolor=#be1313><a href=games/$line[idname].php>";
        echo $line['title']."\n</a>";
        echo "</td></tr><tr><td><img src=images/redthumbbot.gif></td></tr></table></td></tr></table>";
 
}
 
// Closing connection
mysql_close($link);
?>
This is my code to use the data stored in mysql database. How do I filter these results to show only games that under the 'category' section say 'action' etc..
Earlier Tasairis said
You change your query so it only SELECTs the rows WHERE the category is "action".
but I can't get this to work.

Re: using specific mysql data

Posted: Mon Apr 13, 2009 11:53 am
by requinix

Code: Select all

$query = 'SELECT * FROM games WHERE category = "action"';
Seems so simple now, eh?

Re: using specific mysql data

Posted: Mon Apr 13, 2009 5:10 pm
by tomsace
Ah, very nice, don't know why I didn't get it first time around lol!

Re: using specific mysql data

Posted: Tue Apr 14, 2009 5:07 pm
by tomsace
My results are returned in a column and just span across the whole page.
How do I edit my results so that after showing about 7 results, a <br> to go down a page?

Can this be done using the 'limit' code by setting it to 7 and start listing again on the next line?

Re: using specific mysql data

Posted: Tue Apr 14, 2009 6:22 pm
by McInfo
Please provide
  • your table structure
  • some sample data
  • an example of the HTML structure you are trying to achieve
Related: viewtopic.php?p=533030#p533030

Edit: This post was recovered from search engine cache.

Re: using specific mysql data

Posted: Tue Apr 14, 2009 6:41 pm
by tomsace
Hey,
Heres the page http://www.zippygame.com/categories.php?id=all

After the first 7 results, I need a new line to start. As you can see the results carry on beyond the table and it looks very unsightly.

Heres my code:

Code: Select all

// Performing SQL query
$query = 'SELECT * FROM games ORDER BY id DESC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
        echo "<table cellpadding=0 cellspacing=0 border=0 width=97 align=left><tr><td>
              <table cellpadding=0 cellspacing=0 border=0 width=85 align=center>
              <tr><td><img src=images/greenthumbtop.gif></td></tr><tr><td align=center bgcolor=#1bb214>";
        echo "<a href=games/$line[idname].php><img src=games/image/$line[idname].jpg border=0></a></td></tr>
              <tr><td align=center bgcolor=#1bb214><a href=games/$line[idname].php>";
        echo $line['title']."\n</a>";
        echo "</td></tr><tr><td><img src=images/greenthumbbot.gif></td></tr></table></td></tr></table>";
 
}

Re: using specific mysql data

Posted: Tue Apr 14, 2009 7:50 pm
by McInfo
I don't think you understood what I was asking for.

Anyway, here is a generic example.

Code: Select all

<html>
<head><title>Wrapping Table Rows</title></head>
<style type="text/css">
td {
    width: 40px;
    height: 40px;
    background-color: #DEF;
}
</style>
<body>
 
<table cellspacing="4" cellpadding="0" border="1">
<tr>
<?php
$cells_wide = 7;
$cells_max = 42;
for ($cells = 0; $cells < $cells_max; $cells++) {
    if (0 < $cells && 0 == $cells % $cells_wide) {
        echo '</tr><tr>';
    }
    echo '<td>&nbsp;</td>';
}
?>
</tr>
</table>
 
</body>
</html>
If you need to learn about the % operator, visit http://www.php.net/operators.arithmetic.

Edit: Modified loop logic

Edit: This post was recovered from search engine cache.

Re: using specific mysql data

Posted: Wed Apr 15, 2009 11:53 am
by tomsace
Hi,
The script works great.
Except when I try to insert my code:

Code: Select all

// Performing SQL query
$query = 'SELECT * FROM games ORDER BY id DESC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
TEXT
 
}
Before I add this code the table looks like it should except my images and text from the database isn't showing. When I add the code above all the images and text from the database show but...
Well visit http://www.zippygame.com/categories.php?id=all and see...

Any help or advice on how I can add my code to the script but keep it working?




This is my code before I ad the first code:

Code: Select all

<table cellpadding="0" cellspacing="0" border="0"><tr>
 
<?php
 
$cells_wide = 7;
$cells_max = 9;
for ($cells = 0; $cells < $cells_max; $cells++) {
    if (0 < $cells && 0 == $cells % $cells_wide) {
        echo '</tr><tr>';
    }
        echo "<td><table cellpadding=0 cellspacing=0 border=0 width=97 height=115 align=left><tr><td>
              <table cellpadding=0 cellspacing=0 border=0 width=85 align=center>
              <tr><td><img src=images/greenthumbtop.gif></td></tr><tr><td align=center bgcolor=#1bb214>";
        echo "<a href=games/$line[idname].php><img src=games/image/$line[idname].jpg border=0></a></td></tr>
              <tr><td align=center bgcolor=#1bb214><a href=games/$line[idname].php>";
        echo $line['title']."\n</a>";
        echo "</td></tr><tr><td><img src=images/greenthumbbot.gif></td></tr></table</td></tr></table><td>";
}
?>
</tr>
</table>
And this is my code I am using now:

Code: Select all

<table cellpadding="0" cellspacing="0" border="0"><tr>
 
<?php
// Performing SQL query
$query = 'SELECT * FROM games ORDER BY id DESC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
$cells_wide = 7;
$cells_max = 9;
for ($cells = 0; $cells < $cells_max; $cells++) {
    if (0 < $cells && 0 == $cells % $cells_wide) {
        echo '</tr><tr>';
    }
        echo "<td><table cellpadding=0 cellspacing=0 border=0 width=97 height=115 align=left><tr><td>
              <table cellpadding=0 cellspacing=0 border=0 width=85 align=center>
              <tr><td><img src=images/greenthumbtop.gif></td></tr><tr><td align=center bgcolor=#1bb214>";
        echo "<a href=games/$line[idname].php><img src=games/image/$line[idname].jpg border=0></a></td></tr>
              <tr><td align=center bgcolor=#1bb214><a href=games/$line[idname].php>";
        echo $line['title']."\n</a>";
        echo "</td></tr><tr><td><img src=images/greenthumbbot.gif></td></tr></table</td></tr></table><td>";
}
}
?>
</tr>
</table>

Re: using specific mysql data

Posted: Wed Apr 15, 2009 12:42 pm
by McInfo
That example was not meant to be "plug and play". It was a demonstration of how to cause a row break between cells at a regular interval. The for loop in the example represents the while loop in your script.

You are burying yourself in tables. Have you considered first building the page manually to see how the HTML code should look, then adding the PHP when the layout design is complete?

Edit: This post was recovered from search engine cache.