What's wrong with my Script?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

What's wrong with my Script?

Post by Klaws_wolverine »

Hello all,

I have an upcoming contest coming up for the small electrical appliance industry, anyhow, here is my problem.

Users come online, they enter their form information, once they click on submit, the first thing that happens is that the script gets a numeric value from a table having only one row. It takes that value and assigns it as the user's contestandID.
Once it has that contestandID, before it leaves the table, it increments that value in that table by 1 to accomodate the next user to come in. For some reason, this is not working.

If the user bought say a GE product already, then the info goes into the db.
Anyhow, the user gets instant notification whether he is a winner or not.

For instance, say every 49th user gets a prizeA and the next 49th entrant gets prizeB, and which prize he gets, gets recorded into the db.

My script isn't running, I would apreciate any help anyone can provide, here is the relevant code, thanks.


<?php

$age = $HTTP_POST_VARS['age'];
// all http post vars go here for all form variables.


require("dbConnectionStrings.php");
$sql = "SELECT contestantID FROM tableName1";
$result = mysql_query($sql) or die(mysql_error());

$userID = $contestantID++;
$sql2 = "UPDATE tableName1 SET contestantID = '$userID'";
$result2 = mysql_query($sql2) or die(mysql_error());
?>
UserID is: <?php echo "$userID"; ?> // for some reason, userID is always NULL

<?php
function noPrize()
{
$today = date("F j, Y, g:i a");

$sql3 = "INSERT INTO tableName2(contestantID, age, fname, lname, address, city, province, pnumber,contest, Postal, date, type, email, isWinner, prize, purchase)
VALUES ('$contestantID', '$age', '$fname', '$lname', '$address', '$city', '$prov', '$pnumber','$cnt','$postal', '$today', '$type', '$email', 0, 0, 0)";

$result3 = mysql_query($sql3) or die(mysql_error());
}


function prizeA()
{


$sql4 = "INSERT INTO tableName2(contestantID, age,fname,lname,address,city,province, pnumber, Postal, date, type, email, isWinner, prize, purchase
VALUES ('$contestantID', '$age', '$fname', '$lname', '$address', '$city', '$prov', '$pnumber','$cnt','$postal', '$today', '$type', '$email', 1, 1, 0)";

$result4 = mysql_query($sql4) or die(mysql_error());
}


function prizeB()
{


$sql5 = "INSERT INTO tableName2(contestantID, age,fname,lname,address,city,province, pnumber, contest, Postal, date, type, email, isWinner, prize, purchase)
VALUES ('$contestantID', '$age', '$fname', '$lname', '$address', '$city', '$prov', '$pnumber','$cnt','$postal', '$today', '$type', '$email', 1, 2, 0)";

$result5 = mysql_query($sql5) or die(mysql_error());
}
?>
<?php
$sql6="select contestantID, count(contestantID) as winners
from tableName2
where isWinner=1
group by contestantID";
$result6 = mysql_query($sql6) or die(mysql_error());
?>
ContestandID is:<?php echo "$contestantID"; ?><br>UserID is:<?php echo "$userID"; ?><br> Winners:<?php echo "$winners"; ?><br>//both userID and winners are always NULL, don't know why

<?php
if ($winners < 25)
{
if (($contestantID -10) % 98 == 0)
{ prizeB(); }
elseif (($contestantID -10) % 49 == 0)
{ prizeA(); }
else { noPrize(); }
}
else { noPrize(); }

?>

I would apreciate any help anyone can provide, this is driving me nuts, I couldn't figure it out for a days. When submit is clicked, the entrantID in tableName1 doesn't get incremented, and in tableName2, everything is equal to 0 and some values are NULL.

Some plz help.
thanks
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Re: What's wrong with my Script?

Post by microthick »

Klaws_wolverine wrote: <?php

$age = $HTTP_POST_VARS['age'];
// all http post vars go here for all form variables.


require("dbConnectionStrings.php");
$sql = "SELECT contestantID FROM tableName1";
$result = mysql_query($sql) or die(mysql_error());

$userID = $contestantID++;
$sql2 = "UPDATE tableName1 SET contestantID = '$userID'";
$result2 = mysql_query($sql2) or die(mysql_error());
?>
UserID is: <?php echo "$userID"; ?> // for some reason, userID is always NULL
I believe the line:

$userID = $contestantID++;

is at fault.

$contestantID has no value, since you are not using the returned value from the query array.

Instead try:

Code: Select all

$sql = "SELECT contestantID FROM tableName1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result);
$userID = row&#1111;'$contestantID']++;
Or something to that affect.
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

query

Post by Klaws_wolverine »

Hi,

Thanks for the code, I tried it, and I get a parse error for this line:

$userID = row['$contestantID']++;
and yes, contestantID does exist in the DB, lol.

Any idea?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Re: query

Post by microthick »

Klaws_wolverine wrote:Hi,

Thanks for the code, I tried it, and I get a parse error for this line:

$userID = row['$contestantID']++;
and yes, contestantID does exist in the DB, lol.

Any idea?
Ok, I wasn't sure if you could increment an array value like that.

Instead, do something similar but uglier:

$foo = row['$contestantID'];
$userID = $foo++;

And while, contestantID does exist in the DB, no where in your code have you initialized $contestantID to a value. That is why $userID is NULL.

To use the value of contestantID returned from your SELECT query, you must use the myself_fetch_array() function to process the $result.
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

query

Post by Klaws_wolverine »

lol, now $foo = row['$contestantID']; is giving me a parse error.

This is the table where contestandID is

CREATE TABLE `entrantID` (
`contestantID` int(10) default NULL,
`prize` char(2) NOT NULL default ''
) TYPE=MyISAM;


And why would I use fetch_array() when i'm not using arrays?
But I get a blank screen with a parse error on the line above.

Thx
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

Code: Select all

$sql = "SELECT contestantID FROM tableName1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result);
$foo = row&#1111;'$contestantID'];
$userID = $foo++
I'm sure there are other ways of retrieving data from a mysql query, but I've always used mysql_fetch_query(), be it for better or for worse.

In your original code, you simply ran the line:
$result = mysql_query($sql) or die(mysql_error());

As far as I know, this is not sufficient to populate your variable $contestantID with any sort of value.

By using mysql_fetch_array(), the contents of $result gets interpretted into an array, so that if your query returned multiple rows, you'd be able to access them all. I understand that you will only be getting one row back, but there's nothing wrong with a one element array, right?

So now $row is the array that contains the data returned by the query. You can access the value you want by going $row["fieldname"] so in your case, this would be $row["contestantID"].

I don't see any reason why you should be getting a parse error for this.
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

Interesting development

Post by Klaws_wolverine »

Hi,

I modified some of the code to this:

$sql = "SELECT contestantID FROM tableName1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result);
$foo = $row["contestantID"];
$userID = $foo++

?>
UserID is: <?php echo "$userID"; ?><br>
foo is: <?php echo "$foo"; ?><br>

Tell me, when i'm doing an echo of both variables, how come foo is 2 and userID is 1? lol

This is my echo results:
foo is: 2
UserID is:1

Shouldn't it be the reverse?

Also, now the next line is giving me a parse error:

$sql2 = "UPDATE tableName1 SET contestantID = $userID";
$result2 = mysql_query($sql2) or die(mysql_error());

Is there something wrong with my syntax?
Last edited by Klaws_wolverine on Mon Sep 29, 2003 1:21 pm, edited 1 time in total.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Re: Interesting development

Post by microthick »

Klaws_wolverine wrote:Hi,

I modified some of the code to this:

$sql = "SELECT contestantID FROM tableName1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result);
$foo = $row["contestantID"];
$userID = $foo++

?>
UserID is: <?php echo "$userID"; ?><br>
foo is: <?php echo "$foo"; ?><br>

Tell me, when i'm doing an echo of both variables, how come foo is 2 and userID is 1? lol

This is my echo results:
foo is: 2
UserID is:1

Shouldn't it be the reverse?
Ahh, my bad.

The assignment:

$userID = $foo++;

works like this.

First, $userID = $foo, then $foo gets incremented.

Try $userID = ++$foo;
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

Query

Post by Klaws_wolverine »

Hey thanks dude, you really helped out!
It works now. Youpiee.

But my $winners variable is still empty.
So after reading about your $row, I modified my retrievel code for $winners.

<?php
$sql6="select contestantID, count(contestantID) as winners
from tableName2
where isWinner=1
group by contestantID";
$result6 = mysql_query($sql6) or die(mysql_error());

while ($row=mysql_fetch_array($result6)) {
$winners= $row['winners']; }
?>

Will this populate my $winners variable?
Because it's not, $winners is still NULL in my php echos.

Thx
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Re: Query

Post by microthick »

Klaws_wolverine wrote:Hey thanks dude, you really helped out!
It works now. Youpiee.

But my $winners variable is still empty.
So after reading about your $row, I modified my retrievel code for $winners.

<?php
$sql6="select contestantID, count(contestantID) as winners
from tableName2
where isWinner=1
group by contestantID";
$result6 = mysql_query($sql6) or die(mysql_error());

while ($row=mysql_fetch_array($result6)) {
$winners= $row['winners']; }
?>

Will this populate my $winners variable?
Because it's not, $winners is still NULL in my php echos.

Thx
I can't see anything wrong with your code, but my eyes may just be failing me.

What is your goal for this query? What info are you trying to capture?
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

query

Post by Klaws_wolverine »

well see...

I have to allow only a certain number of winners overall, perhaps on a montly basis.

<?php
if ($winners < 25)
{
if (($contestantID -10) % 98 == 0)
{ prizeB(); }
elseif (($contestantID -10) % 49 == 0)
{ prizeA(); }
else { noPrize(); }
}
else { noPrize(); } //since $winners is null only this function gets called

?>

So go to the specified functions as long as the number of winners is below 25 in the DB.

But since $winners is always NULL, this contest won't ever work. lol

Well at least, is there a better way for me to do what I want to do?

In the DB isWinner is int(2).
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

I still can't help you.

A couple starting points for your troubleshooting:

1) Does the while loop iterate the expected number of times?

2) You can use the mysql_num_rows() function to see how many rows were returned with your SELECT statement.

Does your $result6 contain the correct number of rows?

Example code, please modify to work.

Code: Select all

$result = mysql_query("SELECT * FROM table1");
$num_rows = mysql_num_rows($result);
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

query

Post by Klaws_wolverine »

Hi,

ok, i'm using ur num_rows instead, so much easier, lol. And it works.
But now.... I'm hoping this is the last bug in my script..... my script doesn't work, lol.

See..
<?php
if ($num_rows < 25)
{
if (($contestantID -10) % 98 == 0)
{ prizeB(); }
elseif (($contestantID -10) % 49 == 0)
{ prizeA(); }
else { noPrize(); }
}
else { noPrize(); }

?>

So, with the above script, when the contestantID is the 49th user, he should be going to prizeA(), and the next 49th should be going to prizeB(), and the next 49th to prizeA() and so on and so forth.

But it's always going to noPrize(), even when the contestantID is 49.

Is there something wrong with my algorithm?
Thaks

Ok, this is an edit:

I took out the -10, not too sure why I had it there to begin with.
But when it reaches contestantID 49, I get this error:
Column count doesn't match value count at row 1

and that submit doesn't get submited to the DB.

What does that mean?
Klaws_wolverine
Forum Commoner
Posts: 32
Joined: Mon Sep 29, 2003 10:11 am

Query

Post by Klaws_wolverine »

Never mind, it was a missing parameter in the insert.
Thanks for all your help man.

IT WORKS NOW, YOUPIEEEEE>
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

Column count doesn't match value count at row 1

This error normally means you are trying to insert more values into a table than there are available columns. Check your INSERT/UPDATE sql statements.
Post Reply