Cron Job --- PHP [SOLVED]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Cron Job --- PHP [SOLVED]

Post by tecktalkcm0391 »

This script is going to run every couple of hours on a cron job, I know I still have to do the check to see how many items are currently out, but I'll do that later. The problem is that in this:

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?
Last edited by tecktalkcm0391 on Wed Jun 14, 2006 1:24 am, edited 2 times in total.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

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..
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

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.

Code: Select all

//Code Remove for Website Security SORRY!
Within the while() loop, you need to be referencing the data in the $row array, not the $get_updates array.
Ok, yeah, I didn't catch that, I guess I need to watch myself better.
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.
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:

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).
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:
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..
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.


Thanks!
Last edited by tecktalkcm0391 on Wed Jun 14, 2006 1:23 am, edited 2 times in total.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Re: Cron Job --- PHP

Post by tecktalkcm0391 »

I figured out the flaw in my code, thanks!
Post Reply