Using two mysql db tables?

avatar.alex · January 28, 2010

Ok i am building a mod for my game and I am making it so that there are individual shops. One table is the shop and the other is the items. How would I display the items from the items table on the page if I am using the shop table. I want to be able to put the items into the shop. So only the items from the shop will display and not all the items. I think in the blueprint_items I should add shop_id or in the add_item.php figure a way to add the item id to the shops table. I don't know how to approach this.

Shop Table:

CREATE TABLE `shops` (
`id` int(6) NOT NULL auto_increment,
`weapons_id` 
`armor_id` 
`city_id` 
`keepers_name` 
`keepers_dialog` 
`` 
PRIMARY KEY  (`id`),
UNIQUE KEY

Items Table:

CREATE TABLE `blueprint_items` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate latin1_general_ci NOT NULL,
  `description` text collate latin1_general_ci NOT NULL,
  `type` enum('weapon','armour') collate latin1_general_ci NOT NULL,
  `effectiveness` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

jl5501 · January 28, 2010

Firstly the title of your post is a little misleading as there is one db with many tables.

As far as your problem is concerned, then each item in the items table would carry a shop_id to say which shop it belonged to, making it easy to then list items with a particular shop id

avatar.alex · January 28, 2010

So it would be easier to just add a shop ID to the items table. Then how would I go about displaying those items by on the page? I suck at understanding displaying the table info from the id (shops.php?id=X)

jl5501 · January 28, 2010

Ok so if you had a page with a url like that or

shops.php?shopid=123

then in the page shops.php

you could say

$shopid = $_GET['shopid']

and then the query would be like

"select * from items where shopid=$shopid";

avatar.alex · January 28, 2010

I feel like Im missing something?

<?
    /*************************************/
/*           ezRPG script            */
/*         Written by AlexDee          */
/*  http://code.google.com/p/ezrpg   */
/*    http://www.ezrpgproject.com/   */
/*************************************/

include("lib.php");
define("PAGENAME", "Shop");
$player = check_user($secret_key, $db);

$id = $_GET['shopid'];
{
header("Location: citys.php");
}
else
{
$query = $db->execute("select `id`, `shopid`,`name`, `price` from `blueprint_items` where `id`=?", array($_GET['shopid']));

if ($query->recordcount() == 0)
{
	header("Location: cities.php");
}
else
{
	$item = $query->fetchrow();

     while ($item = $query->fetchrow())
		{
			echo "<fieldset>\n";
			echo "<legend><b>" . $item['name'] . "</b></legend>\n";
			echo "<table width=\"100%\">\n";
			echo "<tr><td width=\"85%\">";
			echo $item['description'] . "\n<br />";
			echo "<b>Effectiveness:</b> " . $item['effectiveness'] . "\n";
			echo "</td><td width=\"15%\">";
			echo "<b>Price:</b> " . $item['price'] . "<br />";
			echo "<a href=\"shop.php?act=buy&id=" . $item['id'] . "\">Buy</a><br />";
			echo "</td></tr>\n";
			echo "</table>";
			echo "</fieldset>\n<br />";
			  				}
?>

jl5501 · January 28, 2010

er yes

What is the redirect to cities.php for?

and there is no if there anyway so that it not a valid statement

if(!isset($_GET['shopid']))

{

header("Location: cities.php");

}

$id = $_GET['shopid'];

$query = $db->execute("select `id`, `shopid`,`name`, `price` from `blueprint_items` where `id`=?", array($_GET['shopid']));

if ($query->recordcount() == 0)

{

header("Location: cities.php");

}

else

{

$item = $query->fetchrow();

while ($item = $query->fetchrow())

{

echo "<fieldset>\n";

echo "<legend>" . $item['name'] . "</legend>\n";

echo "<table width=\"100%\">\n";

echo "<tr><td width=\"85%\">";

echo $item['description'] . "\n ";

echo "Effectiveness: " . $item['effectiveness'] . "\n";

echo "</td><td width=\"15%\">";

echo "Price: " . $item['price'] . " ";

echo "<a href=\"shop.php?act=buy&id=" . $item['id'] . "\">Buy</a> ";

echo "</td></tr>\n";

echo "</table>";

echo "</fieldset>\n ";

}

?>

might be more what you want

avatar.alex · January 28, 2010

because once I get done figuring all the bugs out im going to have the individual shops in cities. Individual cities.

avatar.alex · January 28, 2010

Ok this isn't going into the table

CREATE TABLE `shops` (
`id` int(6) NOT NULL auto_increment,
`city_id` int(11) NOT NULL,
`keepers_name` text collate latin1_general_ci NOT NULL,
`keepers_dialog` text collate latin1_general_ci NOT NULL,
PRIMARY KEY  (`id`),
} ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=9

avatar.alex · January 28, 2010

OK im confused now. In order to have items in the shop could I set the shop id from the blueprint_items to the id in the shops table, so then all the items in the blueprint_items will know to show themselves on the page?

jl5501 · January 28, 2010

Your last post is correct as the id of the shops table is to be used for the shopid of the items table, and that is how you know which items belong in which shop

avatar.alex · January 28, 2010

so it should bring up the items just from the shop their assigned.

avatar.alex · January 28, 2010

Ok I set the item in the db:

UPDATE  `alexdee_game`.`blueprint_items` SET  `shopid` =  '1' WHERE  `blueprint_items`.`id` =3 LIMIT 1 ;

and typed in shops.php?shopid=1 and it redirected me to the cities.php?

jl5501 · January 28, 2010

I would need to see how your code reads now, but it looks like your query should read

$query = $db->execute("select `id`, `shopid`,`name`, `price` from `blueprint_items` where `shopid`=?", $_GET['shopid']);

avatar.alex · January 28, 2010

<?php
    /*************************************/
/*           ezRPG script            */
/*         Written by AlexDee          */
/*  http://code.google.com/p/ezrpg   */
/*    http://www.ezrpgproject.com/   */
/*************************************/

include("lib.php");
define("PAGENAME", "Shop");
$player = check_user($secret_key, $db);

if(!isset($_GET['shopid']))
{
    header("Location: cities.php");
}
$id = $_GET['shopid'];

$query = $db->execute("select `id`, `shopid`,`name`, `price` from `blueprint_items` where `shopid`=?", $_GET['shopid']);

if ($query->recordcount() == 0)
{
     header("Location: cities.php");
}
else
{
   $item = $query->fetchrow();
    while ($item = $query->fetchrow())
   {
         echo "<fieldset>\n";
        echo "<legend><b>" . $item['name'] . "</b></legend>\n";
        echo "<table width=\"100%\">\n";
        echo "<tr><td width=\"85%\">";
        echo $item['description'] . "\n<br />";
        echo "<b>Effectiveness:</b> " . $item['effectiveness'] . "\n";
        echo "</td><td width=\"15%\">";
        echo "<b>Price:</b> " . $item['price'] . "<br />";
        echo "<a href=\"shop.php?act=buy&id=" . $item['id'] . "\">Buy</a><br />";
        echo "</td></tr>\n";
       echo "</table>";
      echo "</fieldset>\n<br />";
      }
}
?>/code]

jl5501 · January 28, 2010

That looks like it should work

avatar.alex · January 28, 2010

nothing is showing up lol. It says 404 Not Found but the screen is white.

avatar.alex · January 29, 2010

I can't find anything wrong with this but its not displaying anything on the page. And there are no errors. Could someone review it?

<?php
    /*************************************/
/*           ezRPG script            */
/*         Written by AlexDee          */
/*  http://code.google.com/p/ezrpg   */
/*    http://www.ezrpgproject.com/   */
/*************************************/

include("lib.php");
define("PAGENAME", "Shop");
$player = check_user($secret_key, $db);

if(!isset($_GET['shopid']))
{
    header("Location: cities.php");
}
$id = $_GET['shopid'];

$query = $db->execute("select `id`, `shopid`,`name`, `description`, `effectiveness`, `price` from `blueprint_items` where `shopid`=?", array($_GET['shopid']));
if ($query->recordcount() == 0)
{
     header("Location: cities.php");
}
else
{

     switch($_GET['act'])
{
case "buy":
	if (!$_GET['id']) //No item ID
	{
		header("Location: shop.php");
		break;
	}

	//Select the item from the database
	$query = $db->execute("select `id`, `name`, `price` from `blueprint_items` where `id`=?", array($_GET['id']));

	//Invalid item (it doesn't exist)
	if ($query->recordcount() == 0)
	{
		header("Location: shop.php");
		break;
	}

	$item = $query->fetchrow();
	if ($item['price'] > $player->gold)
	{
		include("templates/private_header.php");
		echo "<b>Shop Keeper:</b><br />\n";
		echo "<i>Sorry, but you cannot afford this!</i><br /><br />\n";
		echo "<a href=\"inventory.php\">Return to inventory</a> | <a href=\"shop.php\">Return to shop</a>";
		include("templates/private_footer.php");
		break;
	}

	$query1 = $db->execute("update `players` set `gold`=? where `id`=?", array($player->gold - $item['price'], $player->id));
	$insert['player_id'] = $player->id;
	$insert['item_id'] = $item['id'];
	$query2 = $db->autoexecute('items', $insert, 'INSERT');
	if ($query1 && $query2) //If successful
	{
		$player = check_user($secret_key, $db); //Get new user stats

		include("templates/private_header.php");
		echo "<b>Shop Keeper:</b><br />\n";
		echo "<i>Thank you, enjoy your new <b>" . $item['name'] . "</b>!</i><br /><br />\n";
		echo "<a href=\"inventory.php\">Return to inventory</a> | <a href=\"shop.php\">Return to shop</a>";
		include("templates/private_footer.php");
		break;
	}
	else
	{
		//Error logging here
	}

	break;

case "sell":
	if (!$_GET['id']) //No item ID
	{
		header("Location: shop.php");
		break;
	}

	//Select the item from the database
	$query = $db->execute("select items.id, blueprint_items.name, blueprint_items.price from `blueprint_items`, `items` where items.item_id=blueprint_items.id and items.player_id=? and items.id=?", array($player->id, $_GET['id']));

	//Either item doesn't exist, or item doesn't belong to user
	if ($query->recordcount() == 0)
	{
		include("templates/private_header.php");
		echo "Sorry, that item does not exist!";
		include("templates/private_footer.php");
		break;
	}

	$sell = $query->fetchrow(); //Get item info

	//Check to make sure clicking Sell wasn't an accident
	if (!$_POST['sure'])
	{
		include("templates/private_header.php");
		echo "Are you sure you want to sell your <b>" . $sell['name'] . "</b> for <b>" . floor($sell['price']/2) . "</b> gold?<br /><br />\n";
		echo "<form method=\"post\" action=\"shop.php?act=sell&id=" . $sell['id'] . "\">\n";
		echo "<input type=\"submit\" name=\"sure\" value=\"Yes, I am sure!\" />\n";
		echo "</form>\n";
		include("templates/private_footer.php");
		break;
	}

	//Delete item from database, add gold to player's account
	$query = $db->execute("delete from `items` where `id`=?", array($sell['id']));
	$query = $db->execute("update `players` set `gold`=? where `id`=?", array($player->gold + floor($sell['price']/2), $player->id));

	$player = check_user($secret_key, $db); //Get updated user info

	include("templates/private_header.php");
	echo "You have sold your <b>" . $sell['name'] . "</b> for <b>" . floor($sell['price']/2) . "</b> gold.<br /><br />\n";
	echo "<a href=\"inventory.php\">Return to inventory</a> | <a href=\"shop.php\">Return to shop</a>";
	include("templates/private_footer.php");
	break;


    while ($item = $query->fetchrow())
   {
         echo "<fieldset>\n";
        echo "<legend><b>" . $item['name'] . "</b></legend>\n";
        echo "<table width=\"100%\">\n";
        echo "<tr><td width=\"85%\">";
        echo $item['description'] . "\n<br />";
        echo "<b>Effectiveness:</b> " . $item['effectiveness'] . "\n";
        echo "</td><td width=\"15%\">";
        echo "<b>Price:</b> " . $item['price'] . "<br />";
        echo "<a href=\"shops.php?shopid="  . $item['price'] . "act=buy&id=" . $item['id'] . "\">Buy</a><br />";
        echo "</td></tr>\n";
       echo "</table>";
      echo "</fieldset>\n<br />";
                             }
               }
}
?>

jl5501 · January 29, 2010

I cannot see anything obviously wrong with it apart from not seeing why you put a single variable into an array for your queries.

but, put this at the top of your page to make any errors show up

ini_set("display_errors","1");

error_reporting(E_ALL);

fenway · February 1, 2010

I'll say it again -- php syntax errors really have no place on this board.

avatar.alex · February 1, 2010

its working. Is there a way to bring up the same ID for two different tables for example

shops.php?shopid=1

brings the info from table shops and blueprint_items from the same id.

avatar.alex · February 1, 2010

There is an error on line 36 which is the first line below. I want to be able to pull information from the same id using shops.php?shopid=1

while ($item = $query->fetchrow() && $shop = $shops->fetchrow())
   {
   	include("templates/private_header.php");
    print "<fieldset>\n";
        print "<legend><b>" . $shop['name'] . "</b></legend>\n";
        print "<table width=\"100%\">\n";
        print "<tr><td width=\"85%\">";
        print $shop['country'] . "\n<br />";
        print "<b>Effectiveness:</b> " . $shop['npc_name'] . "\n";
        print "</td><td width=\"15%\">";
        print "<b>Price:</b> " . $shop['npc_dialog'] . "<br />";
        print "</td></tr>\n";
       print "</table>";
      print "</fieldset>\n<br />";
      
         echo "<fieldset>\n";
        echo "<legend><b>" . $item['name'] . "</b></legend>\n";
        echo "<table width=\"100%\">\n";
        echo "<tr><td width=\"85%\">";
        echo $item['description'] . "\n<br />";
        echo "<b>Effectiveness:</b> " . $item['effectiveness'] . "\n";
        echo "</td><td width=\"15%\">";
        echo "<b>Price:</b> " . $item['price'] . "<br />";
        echo "<a href=\"shop_serv.php?act=buy&id=" . $item['id'] . "\">Buy</a><br />";
        echo "</td></tr>\n";
       echo "</table>";
      echo "</fieldset>\n<br />";
      	include("templates/private_footer.php");         }

Sign In

Using two mysql db tables?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information