Grabbing Name Vaule From Database

[Deanznet]

Dam idk i been trying all day i cant get any results out of the database

[Deanznet]

Been Messing with it all day cant get anything to display..

 

This is like the last part of the code befor its completed and its pissing me off lol haha.. i guess it just takes time..

 

But ya if someone can kinda help me again and see what im doing wrong.

[pocobueno1388]

Okay...tell me exactly what your trying to select from each table.

[Deanznet]

I have an

 

CREATE TABLE `Items` (
  `item_id` int( NOT NULL default '0',
  `item_desc` varchar(255) NOT NULL default '',
  `item_price` int( NOT NULL default '0',
  `item_quanity` int( NOT NULL default '0',
  `item_picture` varchar(255) NOT NULL default '',
  `item_sellback` int( NOT NULL default '0',
  `item_in_shop` tinyint(2) NOT NULL default '0',
  `item_name` varchar(255) NOT NULL default ''
) TYPE=MyISAM;

 

-- 
-- Table structure for table `user_item`
-- 

CREATE TABLE `user_item` (
  `item_id` int( NOT NULL default '0',
  `user_owner_id` int( NOT NULL default '0'
) TYPE=MyISAM;

-- 
-- Dumping data for table `user_item`
-- 

 

This is the persons invetory, so it matchs the $user_id to the user_owner_id in the table User_item than ts going to need to select the

 

item_name

item_desc

item_picture

item_sellback

 

[wsantos]

Try this if this is what you want.

 

SELECT 
  i.item_name AS Name,i.item_desc AS Desc,i.item_picture as Pic,i.item_sellback AS Sell 
  FROM 
    Items i 
  JOIN 
    user_item u 
    ON 
      i.item_id=u.item_id 
  WHERE 
    item_id = $yourvariable;

[Deanznet]

Hey i got this

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Desc,i.item_picture as Pic,i.item_sellback AS Sell FROM

 

Keep getting that for some reason

[wsantos]

It might be a good idea to post also the code that causes those errors as it may be typos.  It just meant that there is a syntax error on your query.  (Usually typos)

Anyway try this

 

SELECT 
  i.item_name,i.item_desc,i.item_picture,i.item_sellback
  FROM 
    Items i 
  JOIN 
    user_item u 
    ON 
      i.item_id=u.item_id 
  WHERE 
    u.user_owner_id = $yourvariable;

 

[Deanznet]

That worked! 

 

But the problem is selling the item/buying the item how would that work?

 

 //get the items price
                $get_price = mysql_query("SELECT item_sellback FROM Items WHERE item_id='$itemID'")
                or die(mysql_error());
                $price = mysql_fetch_assoc($get_price);
                
             
                     
                     $update_q = "UPDATE phpbb_trading SET item_in_shop = '1',  itemid = '0'
                     WHERE item_id = '$itemID'";
                     $update_q_final = mysql_query($update_q)or die(mysql_error().'With Query<p>'.$update_q);
                
                     echo "Item Sold<br>";

 

Thats the code it gets the users money and than also updates it when they hit submit. Getting the item sell back price part should work but not sure how the update part will work. It would need to remove the owner_id i guess from the item and update the item_quanity. Im not sure

 

That grabs the users money

[wsantos]

Ok first...I don't think this is the section that does that for you.  The first part only gets the price of the item.  The second part hardcoded to set the quantity of the items in your shop to be one and the itemid to be 0.  What would I suggest is post your OWN data structure that you plan to use on your site and we will start from there.  As giving you the scripts or queries based on the original template you used would cause more confusion on both you and the members of the forum.

 

1.  Decide what you want to do on your site.  (The flow of events, what you will show, etc...)

2.  Based on the previous item design the structure of your database.  We posted the links that should help you on this.

 

Anyway, try to play with this.

 

UPDATE Items SET item_in_shop = item_in_shop-$order WHERE item_id=$item_id;

[Deanznet]

UPDATE Items SET item_in_shop = item_in_shop-$order WHERE item_id=$item_id;

 

Exactly what dose that do?

 

 

<?php

//select the users money
$query = mysql_query("SELECT user_points FROM phpbb_users WHERE user_id='$user_id'")or die(mysql_error());
$user_points = mysql_fetch_assoc($query);
    
    
    //check if they submitted the form
    if (isset($_POST['submit'])) {
        
        if ($_POST['action'] == 'sell') {
            
            echo "<br>";
            
            //Loop through the selected items
            foreach($_POST['selected'] as $itemID){
                echo $itemID.'<br>';                
                                
                //get the items price
                $get_price = mysql_query("SELECT item_sellback FROM Items WHERE item_id='$itemID'")
                or die(mysql_error());
                $price = mysql_fetch_assoc($get_price);
                
             
                     
                     $update_q = "UPDATE phpbb_trading SET item_in_shop = '1',  itemid = '0'
                     WHERE item_name = '$itemID'";
                     $update_q_final = mysql_query($update_q)or die(mysql_error().'With Query<p>'.$update_q);
                
                     echo "Item Sold<br>";
                     
                    //they have enough, now do a query to add their money
                    $add = "UPDATE phpbb_users SET user_points=user_points+{$price['item_sellback']}
                    WHERE user_id='$user_id'";
                    $add_final = mysql_query($add)or die('ERROR: '.mysql_error().' with query<br>'.$add);
}
        }
    }
    
    $result = mysql_query("SELECT 
  i.item_name,i.item_desc,i.item_picture,i.item_sellback
  FROM 
    Items i 
  JOIN 
    user_item u 
    ON 
      i.item_id=u.item_id 
  WHERE 
    u.user_owner_id = $user_id; ")or die(mysql_error());
    $num_rows = mysql_num_rows($result);
    
    print "<br>There are $num_rows cards in your inventory <P>";
    print "<form action='{$_SERVER['PHP_SELF']}' method='post'>";
    print '<table width=500 height= 100  border=1>'."\n";
    
    while ($get_info = mysql_fetch_assoc($result)) {
        print "<tr>";
        
        print "\t<td><font face=arial size=1/>";
        
        print "<input type='checkbox' name='selected[]' value='{$get_info['item_name']}' />"
        .$get_info['item_name'].'<br><td><center>'.$get_info['item_price'].'<center>'.Boomies.'<br><td><center>Sell Back Price:<center>'.$get_info['item_sellback'].'<br><td><center>'.$get_info['item_desc']."</font></td>";
        print "</tr>";
    }
    
    echo '<select name="action">'
    .'<option value="sell">sell</option>'
    .'</select><p>';
    echo '<input type="submit" name="submit">';
    echo '</form>';
?>

 

Basically that shows the users items.. And it puts a check box next to them and when they hit sell it sells the item and gives the user the money that was in the sellback price.

[wsantos]

Intial values

 

item_iditem_in_shop

1525

2200

 

ther order is the amount you want to sell/buy.after executing of the query with this settings

 

$item_id = 2

$order = 75

 

The result should be

 

item_iditem_in_shop

1525

2125

 

Try to read this http://dev.mysql.com/doc/refman/5.1/en/update.html

 

Basically that shows the users items.. And it puts a check box next to them and when they hit sell it sells the item and gives the user the money that was in the sellback price.

 

Where do you store the user money? What is the complete table structure? What is the table name? and many more...Those are examples of informations essential to achieve your goal.  Also from the script you posted you have a table phpbb_trading...what is it's structure and what is its purpose? This is a card trading game right?

 

[Deanznet]

Okay Sorry This is confusing everyone because i started doing one thing and than apperently i couldent achive me goal that way so now im kinda starting out new.

 

Okay these are the tables that are going to be used the users money is in the phpbb_users table in the -user_points column. And it is a trading card game.

 

phpbb_users
-user_points 

-- 
-- Table structure for table `user_item`
-- 

CREATE TABLE `user_item` (
  `item_id` int( NOT NULL default '0',
  `user_owner_id` int( NOT NULL default '0'
) TYPE=MyISAM;

-- 
-- Dumping data for table `user_item`
-- 

CREATE TABLE `Items` (
  `item_id` int( NOT NULL default '0',
  `item_desc` varchar(255) NOT NULL default '',
  `item_price` int( NOT NULL default '0',
  `item_quanity` int( NOT NULL default '0',
  `item_picture` varchar(255) NOT NULL default '',
  `item_sellback` int( NOT NULL default '0',
  `item_in_shop` tinyint(2) NOT NULL default '0',
  `item_name` varchar(255) NOT NULL default ''
) TYPE=MyISAM;

 

 

 

phpbb_trading was the old database befor i knew i had to do one to many.

 

Thats not going to be used anymore only the Item and user_item tables will be used. I just posted the old code so you could see how it sold the item befor.

 

 

Here is the Updated Code to the point on where we are. So Far this script displays the users invetory it puts a check box next to the item.

 

<?php

//select the users money
$query = mysql_query("SELECT user_points FROM phpbb_users WHERE user_id='$user_id'")or die(mysql_error());
$user_points = mysql_fetch_assoc($query);
    
    
    //check if they submitted the form
    if (isset($_POST['submit'])) {
        
        if ($_POST['action'] == 'sell') {
            
            echo "<br>";
            
            //Loop through the selected items
            foreach($_POST['selected'] as $itemID){
                echo $itemID.'<br>';                
                                
                //get the items price
                $get_price = mysql_query("SELECT item_sellback FROM Items WHERE item_id='$itemID'")
                or die(mysql_error());
                $price = mysql_fetch_assoc($get_price);
                
             
//THIS IS WHERE THE MYSQL QUERY TO SELL THE ITEM SHOULD GO.
                
                     echo "Item Sold<br>";
                     
                    //The query to add their money
                    $add = "UPDATE phpbb_users SET user_points=user_points+{$price['item_sellback']}
                    WHERE user_id='$user_id'";
                    $add_final = mysql_query($add)or die('ERROR: '.mysql_error().' with query<br>'.$add);
}
        }
    }
    
    $result = mysql_query("SELECT 
  i.item_name,i.item_desc,i.item_picture,i.item_sellback
  FROM 
    Items i 
  JOIN 
    user_item u 
    ON 
      i.item_id=u.item_id 
  WHERE 
    u.user_owner_id = $user_id; ")or die(mysql_error());
    $num_rows = mysql_num_rows($result);
    
    print "<br>There are $num_rows cards in your inventory <P>";
    print "<form action='{$_SERVER['PHP_SELF']}' method='post'>";
    print '<table width=500 height= 100  border=1>'."\n";
    
    while ($get_info = mysql_fetch_assoc($result)) {
        print "<tr>";
        
        print "\t<td><font face=arial size=1/>";
        
        print "<input type='checkbox' name='selected[]' value='{$get_info['item_name']}' />"
        .$get_info['item_name'].'<br><td><center>'.$get_info['item_price'].'<center>'.Boomies.'<br><td><center>Sell Back Price:<center>'.$get_info['item_sellback'].'<br><td><center>'.$get_info['item_desc']."</font></td>";
        print "</tr>";
    }
    
    echo '<select name="action">'
    .'<option value="sell">sell</option>'
    .'</select><p>';
    echo '<input type="submit" name="submit">';
    echo '</form>';
?>

 

 

[Deanznet]

http://dev.mysql.com/doc/refman/5.1/en/update.html read that still cant figure it out.

[Deanznet]

can someone please help im almost done and this is the only thing stopping me