Jump to content

Using two mysql db tables?


avatar.alex

Recommended Posts

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 ;

 

Link to comment
https://forums.phpfreaks.com/topic/190128-using-two-mysql-db-tables/
Share on other sites

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

 

 

 

 

 

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 />";
			  				}
?>


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><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 />";

}

?>

might be more what you want

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 

<?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]

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 />";
                             }
               }
}
?>

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);

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");         }

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.