Code: Select all
<?php
//Code Remove for Website Security SORRY![/It only adds one item if evne if the database says the quantity is 100. Can anybody help?
Moderator: General Moderators
Code: Select all
<?php
//Code Remove for Website Security SORRY![/Code: Select all
// this is your single call to fetch the record (fetches last record only)
// initiates the array $get_updates
$get_updates = mysql_fetch_array($get_updates_query);
if(mysql_num_rows($get_updates_query)) {
// here you initiate a new array, $row
while($row = mysql_fetch_assoc($get_updates_query)) {
// starts working with the data within the single call
$quantity = $get_updates['Quantity'];Code: Select all
CREATE TABLE IF NOT EXISTS `users` (
UserID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(64) NOT NULL,
UNIQUE (username)
);
CREATE TABLE IF NOT EXISTS `items` (
ItemID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
item VARCHAR(64) NOT NULL,
UNIQUE (item)
);Code: Select all
CREATE TABLE IF NOT EXISTS `user_items` (
UserID INT UNSIGNED NOT NULL,
ItemID INT UNSIGNED NOT NULL,
UNIQUE (UserID),
UNIQUE (ItemID)
);Ok, yeah, I didn't catch that, I guess I need to watch myself better.bdlang wrote:The reason it's only working with a single record is because your while() loop is iterating over the data within the initial call to mysql_fetch_array(), e.g.Within the while() loop, you need to be referencing the data in the $row array, not the $get_updates array.Code: Select all
//Code Remove for Website Security SORRY!
What I am aiming for is this. I have shops on my website that restock every X hours. When it restocks, it goes to the `update_items` database to see how many of each item it should add. And then it goes into the `items` database to add the items to the shop where the users can then "buy" them from. See each user can have more than one item. So in the `items` database it has a column which has the owner of the item.bdlang wrote: More importantly, focus on why you're having to do that. The script looks like it intends to overwrite every record in the `user_items` table with data from the `update_items` table, and according to you, every two hours.
Why?
What is the ultimate intent?
This completely breaks the reason for using an RDBMS, to not have a relation between two tables, but to flood both tables with redundant, identical data. One table should store the user data, with a unique AUTO_INCREMENT'ed ID value, and the other table should store the item data with the same sort of unique ID. Each table should only store relevant data to itself.
I already have three database, one with the user information, one with every item which has a column that states who the user is, and then I have a database that has the items listes, with a column that says how many should be owned by the shop when the shops need to "restock".bdlang wrote:If you require a relation between the two, i.e. which users have which items, then you link the two tables with an ID value. Ok, having this very basic structure to build on (obviously both tables would have more than just the fields shown), you can do the following:Code: Select all
CREATE TABLE IF NOT EXISTS `users` ( UserID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR(64) NOT NULL, UNIQUE (username) ); CREATE TABLE IF NOT EXISTS `items` ( ItemID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(64) NOT NULL, UNIQUE (item) );This table stores the relation between the other two. You only store the relevant ID keys that relate the two tables, not a bunch of redundant data. Note the use of UNIQUE key contraints on the table, making certain there is only one item per user (which IIRC was your initial intent).Code: Select all
CREATE TABLE IF NOT EXISTS `user_items` ( UserID INT UNSIGNED NOT NULL, ItemID INT UNSIGNED NOT NULL, UNIQUE (UserID), UNIQUE (ItemID) );
I'll take some time a look over the SQL and RDMBS concepts, but I am already a bit familiarized with them. I'll also look over everything eles you said.bdlang wrote:
Depending on your application, your script could update the `user_items` table with the key utilizing mysql_insert_id() or an SQL statement using LAST_INSERT_ID() (a MySQL construct), or you could have a specific script to take an ID from each table based on the current user and perform the INSERT. Either way, this is performed once and then it's done. No need to continue an update process.
The alternative would be to have a single `ItemID` field in your `users` table to reference the specific item that user is allowed to have.
Please take the time to familiarize yourself with SQL and RDMBS concepts prior to building an application utilizing it.
( yes I feel this is important)
The MySQL manual contains a nice tutorial and also covers all the other basic syntax you should know, such as SELECT (and more importantly, JOIN syntax, how to join the related tables). Familiarize yourself with the various data types available for use, as choosing the right data type is as important a factor as any when creating a table structure (note the reference for the UNIQUE key.
Food for thought..