Newbie with a simple problem

d-lexy

I need to write a small php script that would do the following:

Connect to a MySQL database, and check the user_id table for a user number that is entered through an HTML form. If the user number is found, then direct the user to aaa.html page, if not found, direct him to bbb.html page.

Here is what I've come up with:

<?

$host = "localhost";
$user = "username";
$pass = "password";
$dbname = "database";
$table = "user_id";

$connection = @mysql_connect("$host","$user","$pass") or
	die ("Could not connect to database");

// THIS IS THE PART I HAVE NO IDEA HOW TO DO
$query = "SELECT*from $table";
$result = mysql_db_query ($dbname, $query, $connection);
// END

if ($result == $user_id) {
	header ("Location: aaa.html");
} else if ($result) {
	header ("Location: bbb.html");
}
?>

$user_id is the variable passed from the HTML form.
The part that I've marked is probably absolutely useless, since I have no idea what it does. The rest of the code I wrote myself but it probably also has mistakes.

Can anyone give me a hint how to do this. Thanks in advance!!!

kingbear

This is one way to do it:

// THIS IS THE PART I HAVE NO IDEA HOW TO DO
$query = "SELECT*from $table WHERE user_id = $user_id";
$result = mysql_query($query);
// END

if(mysql_num_rows($result) > 0) {
header ("Location: aaa.html");
} else {
header ("Location: bbb.html");
}

d-lexy

Hi Kingbear!

Thank you for your reply.
I tried what you recommended but I end up getting bbb.html wether I have the user_id number stored in the database or not.
Thanks again

tsinka

Hi,

try something like this:

// THIS IS THE PART I HAVE NO IDEA HOW TO DO
$query = "SELECT * from $table WHERE user_id = '$user_id'";
$result = mysql_query($query) or die("SQL error:".mysql_error());
// END

if(mysql_num_rows($result) > 0) {
header ("Location: aaa.html");
} else {
header ("Location: bbb.html");
}

Please post at least the form (better the complete script(s)). What's the method of the form (post or get) ? Depending on that I'd suggest to use either $POST or $GET.

Thomas

d-lexy

hello tsinka,

now I get a message "SQL error:No Database Selected"

I use post method. Here is the source of the html file:

<html>
<head>
<title>Insert your User ID</title>
</head>

<body>
<form name="form1" method="post" action="query.php">
  <input name="user_id" type="text" id="user_id" size="20" maxlength="20">
  <input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>

query.php is the php file that contains the code that we are dealing with. just in case I'll post it again:

<?

$host = "localhost";
$user = "username";
$pass = "password";
$dbname = "database";
$table = "user_id";

$connection = @mysql_connect("$host","$user","$pass") or
	die ("Could not connect to database");

// THIS IS THE PART I HAVE NO IDEA HOW TO DO
$query = "SELECT * from $table WHERE user_id = '$user_id'";
$result = mysql_query($query) or die("SQL error:".mysql_error());
// END

if(mysql_num_rows($result) > 0) {
header ("Location: aaa.html");
} else {
header ("Location: bbb.html");
}
?>

Just a recap on how its supposed to work: A user goes to the html file, the one posted above, enters his number and presses the submit button. The query.php file checks if there is such record in the table user_id. If there is such record, the user is directed to aaa.html if there is no such record, the user is directed to bbb.html

kingbear

oh -- I didn't notice this the first time:

After you connect with the mysql_connect function, you need to select a database like this:

mysql_select_db($dbname, $connection);

Roger_Ramjet

Basic syntax error. The shorthand $user_id is sloppy code at best, but when it refers not to an 'inscript' variable but to a POST or GET var then PHP 4 with globals off will fail.

Always use the long, but trouble free, syntax

$query = "SELECT * FROM " . $_POST['table'] . " WHERE user_id = " . $_POST['user_id'];

//assuming that table and user_id are submitted to the script from a form post. If they originate elsewhere then use the relevent identifier

d-lexy

Thanks again guys,

I worked with what you have provided me with and ended up with this

<?
$host = "localhost";
$user = "username";
$pass = "password";
$dbname = "database";
$table = "user_id";

$connection = @mysql_connect("$host","$user","$pass") or
	die ("Could not connect to database");
$db = mysql_select_db($dbname, $connection);


$query = "SELECT * FROM $table" . " WHERE user_id = " . $_POST['user_id'];
$result = mysql_query($query) or die("SQL error:".mysql_error());


if(mysql_num_rows($result) > 0) {
header ("Location: aaa.html");
} else {
header ("Location: bbb.html");
}
?>

This script seems to work, but only half way.

my user IDs are in a format like creditcard numbers: xxxx xxxx xxxx xxxx
If I enter a value with a space into the html form (example: 0001 0002 0003 0004) I get an error: "SQL error:You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0002 0003 0004' at line 1"
I will get to the aaa.html file if I enter zeros into the html form
If I enter a letter(s) into the form I will get an error "SQL error:Unknown column 'a' in 'where clause'"
if I enter user_id (the name of the field) I will also get to the aaa.html

However, if I enter a numeric value that is stored in the database - lets say I made test number 123456789 - I get to aaa.html

If I enter a numeric value that is not stored in the database, i will get to bbb.html

Roger_Ramjet

Read the manual for MySQL naming conventions and then change your table names. Yours are never going to work as they are, or the effort of getting them to work is just too ridiculous to contemplate. As far as I know, you can't use numbers for names because it confuses the db engine, but you can use numbers within names of course. You can't use names with spaces in them either for exactly the same reason. You may be able to create the table, but you will never be able to query it. MySQL, and any other RDBMS, is going to treat the gaps as the end of a table name and treat the next part as another table name: and you cannot soround table names with qiotes or else how would the db engine differentiat between names and values??

d-lexy

With the knowledge I gathered today, I started the whole thing from the scratch, and here is what I ended up with:

<?php

 //Create a userid variable from the HTTP Post
 $UserId = $_POST[UserId];

 //Make sure that the entered userid is not zero - if so, redirect to bbb.html
 if ($UserId == 0) {
 header ("Location: bbb.html");
 exit;

 //If userid is not zero - Continue
 } else {

 //Connect to DB
 mysql_connect("localhost", "username", "password") or
 die("Could not connect: " . mysql_error());

 //Select database
 mysql_select_db("database");

 //Check the field
 $query = "SELECT * FROM database WHERE user_id = $UserId"; 
 $result = mysql_query($query);

//redirect where needed
if(mysql_num_rows($result) > 0) {
header ("Location: aaa.html");
} else if(mysql_num_rows($result) == 0) {
header ("Location: bbb.html");
}
}
 ?>

This seems to work perfectly. Now I also have a way better understanding of how PHP & MySQL work. Thanks for all your help, I couldn't have done it without you.

tsinka

About that different errors you got:

Always enclose the values in SQL queries with single quites like

SELECT * FROM database WHERE user_id = '$UserId'

especially if the values contain spaces. In your case some of the problems were:

... WHERE user_id = 0001 0002 0003 0004

Syntax error after the first space of the value

... WHERE user_id = '0001 0002 0003 0004' should work

.... WHERE user_id = a

In that case MySQL tries to match the column user_id against a column named a.

.... WHERE user_id = 'a'

should work without an error.

The query only works without single quotes if the values are single numeric values.

Additionally, check if there is a record in the table with user_id='0' (and remove that record if 0 is an invalid user_id)

Thomas

d-lexy

thank you Thomas, now I understand the difference between double and single quotes. A lot of new knowledge gathered today tnx to you guys 🙂

PHPMagician

Please note that this script is suceptible to SQL injection. Doing:

$UserId = intval($UserId);

Should help (unless you are using spaces in your numbers). You will have to come up with some regular expresion or similar. This is very important, ESPECIALLY if you are dealing with credit card. Click the link in my signature and view the section on SQL Injection for more information.