Item Handling with Database [SOLVED]
Moderator: General Moderators
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
Item Handling with Database [SOLVED]
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?
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?
Last edited by tecktalkcm0391 on Wed Jun 14, 2006 2:26 am, edited 4 times in total.
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
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
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
Code: Select all
SELECT * FROM `items`- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
lol are you asking me or telling me?
If you don't know how to use mysql_fetch_array() check the manual.
If you don't know how to use mysql_fetch_array() check the manual.
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
Ok, now I am a little confused. Say there are 3 items that a user has which are recoreded in the database. Like this:
And on the page I want it show up just 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
What would be the query to get only User1's items out of the database, and then show them the example.[IMAGE-Toast]
Toast
[IMAGE-Eggs]
Eggs
[IMAGE-Milk]
Milk
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
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;
Last edited by Christopher on Sun Jun 11, 2006 4:37 pm, edited 2 times in total.
(#10850)
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";
}- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
Ok, this is what I have:
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:
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:
If there are 18 items.[Item]........[Item]........[Item]........[Item]........[Item]........
[Item]........[Item]........[Item]........[Item]........[Item]........
[Item]........[Item]........[Item]........[Item]........[Item]........
[Item]........[Item]........[Item]
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
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";
}
?>
Last edited by tecktalkcm0391 on Wed Jun 14, 2006 3:08 am, edited 1 time in total.
- tecktalkcm0391
- DevNet Resident
- Posts: 1030
- Joined: Fri May 26, 2006 9:25 am
- Location: Florida
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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
users
users_items
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.
items
Code: Select all
item_id (primary)
item_name
item_available (boolean)Code: Select all
user_id (primary)
user_name
...Code: Select all
user_id (primary)
item_id (primary)