[SOLVED] PHP/MySQL Data retrieval in Tables (Generating an URL)

Malevolence · December 29, 2007

Hi There,

First off; sorry about the weird title but I really did not know what to put there....

OK, I am in the middle of writing a little script for a friend of mine's site. He owns some sort of RuneScape related site; and I am writing an Items Database for him. This database is a MySQL Table called 'items' before we go any further. This table contains the following Fields:

itemid, itemname, itemtype, streetprice, highalch, lowalch, memberitem, stackable, quest, tradable, stats, reqtowear, usedfor, effects, itemoptions, respawnareas, wherefound, notes, examine, username, imgurl

Please see This page to see the initial Database: http://www.runescapez.com/itemsdb.php

Please see This page to see my attempt to add an URL to the 'itemname' field so that it generates a 'GET' style URL showing 'itemid' after the ?itemid= of that row: http://www.runescapez.com/itemsdbbeta.php

As you can see; it removes some data rows and if you hover your mouse over the hyperlinks, MUCH more appears rather than a simple link like this: http://www.runescapez.com/itempage?itemid=0007, it comes up with the rest of the MySQL Rows IN the link for some reason: http://www.runescapez.com/itempage?itemid=0007 -> NameofItem --> Loads of other stuff....

The Bottom line is; I was up late last night trying to fix this.... All I came up with was an everlasting 'while' loop which made my CPU usage go from 0.3% to 100% in under a minute and the fan was shouting at me.... Lol. Yeah. 3.00GHz couldn't take it.....

What I want to do is to make the FIRST column (that's the Itemname) turn all the data into hyperlinks that look like this:

http://www.runescapez.com/itempage.php?itemid=0042 or something like that so that I can make 'itempage.php' use the Php GET method to show a page FULL of data of just that Item. Keep in mind that ALL of this is dynamic (The database appears with a $row variable that was written in the meat of the code. If you look below; you'll see what I did right and what I did WRONG ??? :-\

THIS is the code for 'http://www.runescapez.com/itemsdbbeta.php' (Minus the server passwords and names etc.)

<?php

[HIDDEN PASSWORD/DETAILS]
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
?>

<html>
<head>
<title>RuneScapez.com :: Items Database</title>

<style>useless in this post</style>

</head>

<body>

//useless in this post

//below grabs all the data from the database in order of item name ascending

<?php
$result = mysql_query("SELECT * FROM items ORDER BY itemname ASC");

echo "<table border='1'>
<tr>
<th>Item Name</th>
<th>Item Type</th>
<th>Street Price</th>
<th>High Alch Reward</th>
<th>General Store Price</th>
<th>Members Item?</th>
<th>Stackable?</th>
<th>Quest?</th>
<th>Tradable?</th>
<th>Examine</th>
</tr>";

//if I'm correct, the below fetches all the 'results' from the database based on $result (set earlier in the code) in rows.

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . "<a href='www.runescapez.com/itempage.php?itemid=" . $row['itemid'] . ">" . $row['itemname'] . "</a>";
  echo "<td>" . $row['itemtype'] . "</td>";
  echo "<td>" . $row['streetprice'] . "</td>";
  echo "<td>" . $row['highalch'] . "</td>";
  echo "<td>" . $row['lowalch'] . "</td>";
  echo "<td>" . $row['memberitem'] . "</td>";
  echo "<td>" . $row['stackable'] . "</td>";
  echo "<td>" . $row['quest'] . "</td>";
  echo "<td>" . $row['tradable'] . "</td>";
  echo "<td>" . $row['examine'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
?>
  </font></p>
</body>
</html>

This is the line which gets the URL:

echo "<td>" . "<a href='www.runescapez.com/itempage.php?itemid=" . $row['itemid'] . ">" . $row['itemname'] . "</a>";

I can see that the problem is that there are two $row[''] variables in the same row, but how do I fix this? I don't know how to retrieve single pieces of data from MySQL.... Perhaps I need to write a different variable? I did try doing seperate loops but this ended up sending my PC into space with neverending loops of a loop.

Could someone help me out?

Thanks in advance,

Malev.

redarrow · December 29, 2007

try this please...

<?php

[HIDDEN PASSWORD/DETAILS]
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
?>

<html>
<head>
<title>RuneScapez.com :: Items Database</title>

<style>useless in this post</style>

</head>

<body>

//useless in this post

//below grabs all the data from the database in order of item name ascending

<?php
$result = mysql_query("SELECT * FROM items ORDER BY itemname ASC");

echo "<table border='1'>
<tr>
<th>Item Name</th>
<th>Item Type</th>
<th>Street Price</th>
<th>High Alch Reward</th>
<th>General Store Price</th>
<th>Members Item?</th>
<th>Stackable?</th>
<th>Quest?</th>
<th>Tradable?</th>
<th>Examine</th>
</tr>";

//if I'm correct, the below fetches all the 'results' from the database based on $result (set earlier in the code) in rows.

while($row = mysql_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td>";
echo "<a href='itempage.php?itemid=".$row['itemid']."'>".$row['itemname']."</a>";
  echo "</td>";
  echo "<td>" . $row['itemtype'] . "</td>";
  echo "<td>" . $row['streetprice'] . "</td>";
  echo "<td>" . $row['highalch'] . "</td>";
  echo "<td>" . $row['lowalch'] . "</td>";
  echo "<td>" . $row['memberitem'] . "</td>";
  echo "<td>" . $row['stackable'] . "</td>";
  echo "<td>" . $row['quest'] . "</td>";
  echo "<td>" . $row['tradable'] . "</td>";
  echo "<td>" . $row['examine'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
?>
  </font></p>
</body>
</html>

Malevolence · December 29, 2007

Well first of all; thanks as it SLIGHTLY works.

You see; I'm faced with a new problem.

Goto: http://www.runescapez.com/itemsdbbeta.php

Yeah......

I can't believe it was a simply mistake like that; Thank You! But I need to work out how to put those into their own column now.... As they've moved.

redarrow · December 29, 2007

have another look just corrected that ok

simcoweb · December 29, 2007

redarrow's snippet will fix your problem. But just a bit of FYI on this so you understand.

1. you can have multiple $row[''] variables in any line if formatted properly

2. in your <a href= you either have to use just the page name like redarrow shows or the full URL including the http:// part. Since you used just the www. it automatically places your full url again in front of that which was causing the double address.

Hope this helps for the future

Malevolence · December 29, 2007

Yeah. Thanks you two; you've really helped me. The reason it wasn't a full url (http://) is because // is a comment; well thats how it was coming up for me.

Regards,

Malev.

redarrow · December 29, 2007

can you format the page properly and let's see if all the itemid's work please......

simcoweb · December 29, 2007

This is where you have to be careful with your quotation marks. As long as you use single quotes on the href= content then the two //'s won't be considered PHP comment starters.

Normal: <a href="http://www.yoursite.com">Your Site</a>

When wrapped in PHP code: <a href='http://www.yoursite.com'>Your Site</a>

Malevolence · December 29, 2007

I haven't put all the item Id's into the database yet.... But it does work.

Keep an eye on it; They should all have Item ID's

Yep, they're all finished. Check to see them working (I'm now working on the itempage.php page)

Sign In

[SOLVED] PHP/MySQL Data retrieval in Tables (Generating an URL)

Recommended Posts

Malevolence

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

Malevolence

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

simcoweb

Link to comment

Share on other sites

Malevolence

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

simcoweb

Link to comment

Share on other sites

Malevolence

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information