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.
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'];
Within the while() loop, you need to be referencing the data in the
$row array, not the
$get_updates array.
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.
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)
);
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 `user_items` (
UserID INT UNSIGNED NOT NULL,
ItemID INT UNSIGNED NOT NULL,
UNIQUE (UserID),
UNIQUE (ItemID)
);
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).
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..