using specific mysql data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

using specific mysql data

Post 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?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: using specific mysql data

Post 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.
Last edited by McInfo on Mon Jun 14, 2010 1:36 pm, edited 1 time in total.
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: using specific mysql data

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: using specific mysql data

Post by requinix »

You change your query so it only SELECTs the rows WHERE the category is "action".
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: using specific mysql data

Post 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.
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: using specific mysql data

Post by tomsace »

Doesn't matter, I have figured it out!
I used the line:
<img src="images/$line[title]" height="75" width="75">...
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: using specific mysql data

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: using specific mysql data

Post by requinix »

Code: Select all

$query = 'SELECT * FROM games WHERE category = "action"';
Seems so simple now, eh?
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: using specific mysql data

Post by tomsace »

Ah, very nice, don't know why I didn't get it first time around lol!
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: using specific mysql data

Post 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?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: using specific mysql data

Post 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.
Last edited by McInfo on Mon Jun 14, 2010 1:37 pm, edited 1 time in total.
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: using specific mysql data

Post 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>";
 
}
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: using specific mysql data

Post 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.
Last edited by McInfo on Mon Jun 14, 2010 1:39 pm, edited 1 time in total.
tomsace
Forum Contributor
Posts: 167
Joined: Thu Jan 01, 2009 8:07 pm

Re: using specific mysql data

Post 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>
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: using specific mysql data

Post 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.
Last edited by McInfo on Mon Jun 14, 2010 1:40 pm, edited 1 time in total.
Post Reply