Page 1 of 2
Item Handling with Database [SOLVED]
Posted: Sun Jun 11, 2006 9:04 am
by tecktalkcm0391
Each user on my website can "buy" items (with website points), for their account. I need to have it so that each item on my website has a unique number along with a description and name of the item. I know how to do that, but I wanted to see what you guys have to say about this:
How can I make it to that only one user can have an item? Should I add another column to the database for the user, in which, the username of the person that "has" that item?
Also, how would I show all of the items on the page, using a query to my MySQL Database?
Posted: Sun Jun 11, 2006 9:42 am
by jayshields
I don't know what the problem is really.
First of all, yes, if you have so many items, but only one user can have each item, add a user column with the owners username in it, or their user ID.
To grab all the items use
Posted: Sun Jun 11, 2006 11:03 am
by tecktalkcm0391
Ok, yeah there wasn't really a problem, I just wanted input.
When I want to call it I just would use mysql_fetch_array(); and then call them like I would call values from an array ( $item[#] )
Posted: Sun Jun 11, 2006 11:45 am
by jayshields
lol are you asking me or telling me?
If you don't know how to use mysql_fetch_array() check the
manual.
Posted: Sun Jun 11, 2006 11:47 am
by tecktalkcm0391
Yes, I know how to use it I was just making sure I was right.
What I meant to say is:
"When I want to call it, would I just would use mysql_fetch_array(); and then call them like I would call values from an array ( $item[#] )?"
Posted: Sun Jun 11, 2006 3:41 pm
by tecktalkcm0391
Ok, now I am a little confused. Say there are 3 items that a user has which are recoreded in the database. Like this:
Item Number .....................Item Name.................Image URL............... Username
111111...................................Toast.................toast.jpg....................................User1
111112...................................Eggs.................eggs.jpg......................................User1
------------------------------
111120...................................Milk...................milk.jpg.......................................User2
------------------------------
111124...................................Milk...................milk.jpg.......................................User1
And on the page I want it show up just like this:
[IMAGE-Toast]
Toast
[IMAGE-Eggs]
Eggs
[IMAGE-Milk]
Milk
What would be the query to get only User1's items out of the database, and then show them the example.
Posted: Sun Jun 11, 2006 3:46 pm
by derchris
I thought you know how do use it
Code: Select all
SELECT * FROM `items` WHERE username='user1'
Posted: Sun Jun 11, 2006 4:03 pm
by tecktalkcm0391
I do, I know that part, but how do you show the items collected in the mysql_fetch_array();
Posted: Sun Jun 11, 2006 4:30 pm
by Christopher
How about using MVC all in one script:
Code: Select all
// this section of the code is the Controller
// do initialization and get values from the request here
$rows = array();
$link = mysql_connect($Config['DB_DSN']);
mysql_select_db($Config['DB_NAME']);
$user = preg_replace('/[^a-zA-Z0-9]/', '', $_GET['user']);
// this section of the code is the Model
// if gets data from the datasource and does any processing on that data
// the Model is dependent on $user and $link from the Controller
if ($user) {
$sql = "SELECT * FROM `items` WHERE username='$user'";
$result = mysql_query($sql, $link);
if (mysql_error($link)) {
$errmsg = mysql_errno($link);
} else {
while ($row = mysql_fetch_assoc($result)) {
$rows[] = $row;
}
}
} else {
$errmsg = 'No user specified';
}
// this section of the code is the View
// the View is the presentation code and generates HTML using data from the Model
// the View is dependent on $rows and $errmsg from the Model
$content = '';
if ($rows) {
foreach ($rows as $row) {
$content .= '<p><img src="images/' . $row['url'] . '"/><br/>' . $row['name'] . '</p>';
}
} elseif ($errmsg) {
$content .= $errmsg;
} else {
$content .= 'No images for this user.';
}
echo $content;
Posted: Sun Jun 11, 2006 4:34 pm
by derchris
Code: Select all
@mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS) OR die(mysql_error());
mysql_select_db(MYSQL_DATABASE) OR die(mysql_error());
$sql = "SELECT * FROM `items` WHERE username='user1' ";
$result = mysql_query($sql) OR die(mysql_error());
if(mysql_num_rows($result)) {
echo "<table border='1''>\n";
echo "<tr><td>item</td><td>image</td></tr>\n";
while($row = mysql_fetch_assoc($result)) {
$item = $row['item'];
$image = $row['image'];
echo "<tr>
<td>
$item</td>
<td>
<img src=$image border=0></td>
</tr>";
}
echo "</table>\n";
} else {
echo "Nothing in Database\n";
}
Posted: Sun Jun 11, 2006 10:33 pm
by tecktalkcm0391
Ok, this is what I have:
Code: Select all
<?php
require("databaseconnection.php");
$sql = "SELECT * FROM `items` WHERE Owner='{$username}' ");
$result = mysql_query($sql) OR die(mysql_error());
$c=1;
$r=1;
if(mysql_num_rows($result)) {
echo "<table cellpadding=\"8\" align=\"center\" border=\"0\">";
echo "<tbody>\n";
while($row = mysql_fetch_assoc($result)) {
if($c=1 || $c=6 || $c=11 || $c=16 || $c=21 || $c=26 || $c=31 || $c=36 || $c=41 || $c=46){
echo "<tr>";
$r = $r++;
}
$id = $row['ID'];
$name = $row['Name'];
$image = $row['Picture'];
$condition = $row['Condition'];
$description = $row['Description'];
echo "<td valign=\"top\" align=\"middle\" width=\"100\">
<a onclick=\"openwin($id); return false;\" href=\"javascript:;\">
<img title=\"$description\" height=\"80\" alt=\"$description\" src=\"$image\" width=\"80\" border=\"1\" /></a><br />
$name<br /> <strong>$condition</strong><br /> </td>";
$c = $c++;
}
echo "</table>\n";
} else {
echo "You do not have any items!\n";
}
?>
Problem: It keeps making a new row for each item, I want it to make a new row only after that row has 5 items in it!
So I would be like:
[Item]........[Item]........[Item]........[Item]........[Item]........
[Item]........[Item]........[Item]........[Item]........[Item]........
[Item]........[Item]........[Item]........[Item]........[Item]........
[Item]........[Item]........[Item]
If there are 18 items.
Posted: Mon Jun 12, 2006 9:23 am
by tecktalkcm0391
Is their something wrong with the if($c=1 || ... statment? Or what is it. I have tried many things and its still not working!
Posted: Mon Jun 12, 2006 9:49 am
by tecktalkcm0391
Here is my revised code:
Code: Select all
<?php
require("database.php");
$sql = "SELECT * FROM `items` WHERE Owner='{$username}' ";
$result = mysql_query($sql) OR die(mysql_error());
$sc=0;
$c = $sc++;
if(mysql_num_rows($result)) {
echo "<table cellpadding=\"8\" align=\"center\" border=\"0\">";
echo "<tbody>\n";
while($row = mysql_fetch_assoc($result)) {
if($c==1 || $c==6 || $c==11 || $c==16 || $c==21 || $c==26 || $c==31 || $c==36 || $c==41 || $c==46){
echo "<tr>";
}
$id = $row['ID'];
$name = $row['Name'];
$image = $row['Picture'];
$condition = $row['Condition'];
$description = $row['Description'];
echo "<td valign=\"top\" align=\"middle\" width=\"100\">
<a onclick=\"openwin($id); return false;\" href=\"javascript:;\">
<img title=\"$description\" height=\"80\" alt=\"$description\" src=\"$image\" width=\"80\" border=\"1\" /></a><br />
$name<br /> <strong>$condition</strong><br /> </td>";
$c = $c++;
if($c==5 || $c==10 || $c==15 || $c==20 || $c==25 || $c==30 || $c== 35 || $c==40 || $c=45 ||$c==50){
echo "</tr>";
}
}
echo "</table>\n";
} else {
echo "You do not have any items!\n";
}
?>
Posted: Wed Jun 14, 2006 1:01 am
by tecktalkcm0391
I still can't seem to get this to work. If anyone could point me in the right direction, or tell me what to look for or read, that would be awsome!
Posted: Wed Jun 14, 2006 2:14 am
by RobertGonzalez
Your query should be a join query. If you are restricting an item to one user but might use the items again later, I would set a true/false (or 0/1) field in the items table. When a user grabs it and buys it, set the field to false (or 0). That way you don't have phantom items in the table. Your structure could be something like this...
items
Code: Select all
item_id (primary)
item_name
item_available (boolean)
users
users_items
Code: Select all
user_id (primary)
item_id (primary)
Then just run a join query to grab all items in the items table that match up to a user in the users table based on a join with the users_items table.