Help with code

[tinamiller1]

I need some help if anyone can help me with this. I need to have a sql query that reads from two tables that have different info in them. 1 is the datecreation and the other is the quantity, itemnumber and name. then once it gets that I want it to update based on the datecreated and quantity the inventory table. I have a paypal script that auto writes to my 2 tables but couldn't ever figure out how to input an update of the inventory when the customer buys. so i just created a script the user has to run everyday to update inventory. i have been working on this since mid july and done so many diff things and this is like my final crack at this. please please someone out there help me...    the code is wrong cuz it does nothing. someone in another forum said have you tried chron job execute and I am like I don't know what that is the code is wrong so how can I do some sort of automated job????

 

<?php

//DB connect creds
$DB_Server = "mysql"; //your MySQL Server
$DB_Username = "username"; //your MySQL User Name
$DB_Password = "userpassword"; //your MySQL Password
$DB_DBName = "mydatabase"; //your MySQL Database Name

//$quantity = paypal_cart_info.$quantity;

//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());

//inventory handling

$result=mysql_query("SELECT paypal_payment_info.datecreation, paypal_cart_info.itemname, paypal_cart_info.itemnumber, paypal_cart_info.quantity
FROM paypal_payment_info INNER JOIN paypal_cart_info ON paypal_payment_info.txnid = paypal_cart_info.txnid
WHERE (((paypal_payment_info.datecreation)=Date())));

$strquery = sprintf("UPDATE Inventory SET 'numinstock' = numinstock - ", 
$quantity . " WHERE Itemnumber = " . $Itemnumber);
mysql_query($strquery);

?>

 

 

[AndyB]

Here's a start:

 

First problem - you may have connected to the MySQL server but you haven't selected a database.

 

Second problem - your first 'query' has no closing quote. You never do anything with that SELECT query, but that's beside the point.

 

Third problem - you have zero error trapping on either the query that's never used or the UPDATE query. Either would have revealed the lack of database selection.

 

Fourth problem - a minor one. Use [ code ] tags when posting code here.

[tinamiller1]

I took my db stuff out for security when posting. had someone else tell me not to put my info in.

[aebstract]

He is aware that you took the information out for your own security, but you still aren't choosing a database to connect to regardless of what anonymous word you want to use for it.

[gizmola]

What they were saying is that on a Unix system there is a generic scheduling daemon called Cron.  If your server is unix or linux, then cron is available to you. 

 

If your update script works and can be called via the command line PHP, then you should be able to schedule it to run using Cron by making a cron entry using the crontab program. 

 

Crontab entries specify the schedule, and follow a format that is documented in the man page, and in many places on the internet, easily found with google.  For example, su to root and use:

 

crontab -e

 

You should be popped into vi where you'll see the existing crontab.

 

What cron needs in the crontab entry is the the name of the program to run, so you'll have something like:

 

 

29  *  *  *  *  *  /usr/bin/php -f /path/to/yourscript.php  > /dev/null

 

The '*' indicates that for that element of time (minute, hour, day of month, month of year, day of week) to match all possibilities.  A literal number matches only that time.  So in the example above I'm specifying to "run this program on the Half hour, once every hour of every day."

 

Hopefully this is enough to get you going, and have your scheduled inventory job running.

 

 

[AndyB]

To confirm ... mysql_select_db(dbname) is missing from your code.

 

@gizmola - er, wrong thread man

[tinamiller1]

AH I see what he was saying now. I don't understand comments sometimes.

[tinamiller1]

I am on windows vista and this is a site hosted by yahoo merchant. no unix

[gizmola]

To confirm ... mysql_select_db(dbname) is missing from your code.

 

@gizmola - er, wrong thread man

 

Andy my friend -- just responding to the portion of this question that referred to "chron" --

 

I have a paypal script that auto writes to my 2 tables but couldn't ever figure out how to input an update of the inventory when the customer buys. so i just created a script the user has to run everyday to update inventory. i have been working on this since mid july and done so many diff things and this is like my final crack at this. please please someone out there help me...    the code is wrong cuz it does nothing. someone in another forum said have you tried chron job execute and I am like I don't know what that is the code is wrong so how can I do some sort of automated job????

 

Hopefully you guys will get the logic sorted, but obviously a loop is needed at very least   

 

A job that runs on the server is ideal, but it appeared to me from a quick google that Yahoo merchant doesn't offer this option.  Although it's far from ideal, you can get around this by putting the script in webspace, and having another server with a scheduler run it for you.  Yahoo serving looks pretty crappy if they don't even offer you a way of scheduling a job.  There are of course other ways to hack something up, but it seems like we'd be getting to a point where we're no longer teaching someone how to fish, but handing them a bucket of fish instead.

[gizmola]

I agree with everything Andy wrote, and I can add to it:

 

Do you really understand your tables?  This is how I approach most queries:

 

1. Write the query using a literal example and run it in phpMyAdmin.  Does the query return the right columns?

2. Replace the literal portion with a variable as needed.

 

You should test some semblence of this:

 

SELECT paypal_payment_info.datecreation, paypal_cart_info.itemname, paypal_cart_info.itemnumber, paypal_cart_info.quantity
FROM paypal_payment_info 
INNER JOIN paypal_cart_info ON paypal_payment_info.txnid = paypal_cart_info.txnid
WHERE paypal_payment_info.datecreation = 'some date that matches format of datecreation';

 

Since we don't know what the datatypes are we're just guessing, but one thing you can't do is mix in a php function in the middle of a string and have it interpolate.  You need to get the value first, assign it to a variable and then include it.  Also, you have lots of spurious parens that aren't doing anything.  Only add parens when you're sure you need them.

 

After your first query runs, assuming you have a result set, you then need to fetch the rows in a loop, and inside of this loop you will do your UPDATE query.  Again, there are just sooo many examples of this, you should be able to come up with something that resembles a working solution given a little reading.