Item Handling with Database [SOLVED]

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

User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Item Handling with Database [SOLVED]

Post 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?
Last edited by tecktalkcm0391 on Wed Jun 14, 2006 2:26 am, edited 4 times in total.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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

Code: Select all

SELECT * FROM `items`
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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[#] )
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

lol are you asking me or telling me?

If you don't know how to use mysql_fetch_array() check the manual.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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[#] )?"
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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.
derchris
Forum Commoner
Posts: 44
Joined: Sat Jun 10, 2006 6:14 pm

Post by derchris »

I thought you know how do use it 8O

Code: Select all

SELECT * FROM `items` WHERE username='user1'
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

I do, I know that part, but how do you show the items collected in the mysql_fetch_array();
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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;
Last edited by Christopher on Sun Jun 11, 2006 4:37 pm, edited 2 times in total.
(#10850)
derchris
Forum Commoner
Posts: 44
Joined: Sat Jun 10, 2006 6:14 pm

Post 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";
}
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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 />        &nbsp;<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.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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!
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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 />        &nbsp;<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.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post 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!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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

Code: Select all

user_id (primary)
user_name
...
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.
Post Reply