PHP and MYSQL trouble


#1

I have a database setup that has a category called type. I want to have PHP count the number of items of a specific type and store the number in a variable.

for example if i had items that were either type red or type blue and I wanted to cound how many blue items their what would the php code look like to count up the number blue items.


#2

Moved to MySQL.

This is a SQL problem, and a very basic one at that.
A simple select query would do the job:

SELECT COUNT(*) FROM items WHERE type = ‘blue’

Found straight from the MySQL manual.


#3

i am having problems with this code when i try to insert $_SESSION[‘user_ref’] into my insert string. this stores the user id. the code works when this is removed from the string. the session works because i am displaying the username on the page. I am stumped any ideas?

[php]if (isset($_SESSION[‘user_ref’])){
$name=mysql_query(“select FirstName from users where UserID = “.$_SESSION[‘user_ref’].””);
}
while($row = mysql_fetch_array( $name )) {

if (isset($_POST[‘UserName’])) {

if (!$_POST[‘FirstName’] | !$_POST[‘LastName’] | !$_POST[‘Telephone’]) {
die(‘You did not complete all of the required fields’);
}

if (!get_magic_quotes_gpc()) {
$_POST[‘Telephone’] = addslashes($_POST[‘Telephone’]);
}
$usercheck = $_POST[‘Telephone’];
$check = mysql_query(“SELECT telephone FROM referral WHERE username = ‘$usercheck’”)
or die(mysql_error());
$check2 = mysql_num_rows($check);

if ($check2 != 0) {
die(‘Sorry, the ‘.$_POST[‘Telephone’].’ has already been referred.’);
}

$insert = “INSERT INTO referral (user_id, first_name, last_name, telephone, company)
VALUES (’”.$_SESSION[‘user_ref’]."’, ‘".$_POST[‘FirstName’]."’, ‘".$_POST[‘LastName’]."’, ‘".$_POST[‘Email’]."’, ‘".$_POST[‘Telephone’]."’, ‘".$_POST[‘Company’]."’)";

$add_member = mysql_query($insert);[/php]

I know this is long but I want you to see exactly what its doing.

Mod Edit: Added [php] tags for readability.


#4

Have you tried Debugging?
If you echo the query you’re running, what does it look like? What’s the value of the $_SESSION[‘user_ref’] variable? Is MySQL returning any errors (use mysql_error() to check this)?


#5

[php]<?
$name=mysql_query(“SELECT FirstName FROM users WHERE UserID = '” . $_SESSION[‘user_ref’] . “’”);
?>[/php]

Without taking a good hard look, this is the frist thing I see that could be a problem.


#6

True, but if $_SESSION[‘user_ref’] contains an integer (which I presume the query is expecting, considering it’s checked on a userID), it shouldn’t matter. But you’re right when you say it could be a problem.


#7

the value of $_SESSION[‘user_ref’] is the user id. the query that i select the user firstname works because i can display the users name on the page. the insert works when i do not insert the user id into the insert string but when i do use this it seems to drop straight through the code with no errors when i try to debug. i have a meta tag set to go to the confirmation page when the form is completed but when i load this referral page with the $_SESSION in the insert string it hits that meta tag and just shoots to the next page. i have tried removing the meta tag but then i get nothing.


#8

If the userID column is set to auto_increment, then afaik it’s not possible to explicitly set it. MySQL will insert the next consecutive value for it by itself. What I always do is pass ‘null’ as a value (so without the quotes). MySQL doesn’t complain, but whether or not it’s best practice … not sure.


#9

the $_SESSION[‘user_ref’] is already in the user db as userid set up during registration pulled at login. when inserting to the referral db this is a reference column that is not auto_incremented. the referral id is auto incremented. after looking at my code again i see alot of errors. i will work on those and post again thanks for all of your input


#10

i got the code working thanks to the tips on here Thanks again


#11

@zypppora:

True… I must of not even looked at the field name. :) But that is why I put could be the problem because I figured it should have passed mysql error if that was the problem.