[RESOLVED] SELECT statement help

garrickp

Hello all,
I am new to php & MySQL and need a little help. I am sure this issue may be very easy for most of you but here it goes. I am going to have a form with a single field and a submit button.


<form action="" method="get">
	<label name="number" value="Please enter number">Please enter number:</label>
    <input type="text" />
    <input type="submit" value="Check Results"></button>
</form>

When submitted I would like the form to query the database and return a message and echo the "group" field of the database if the data entered matches the data in "number" field in the database. I believe I need to use the SELECT statement to get this done. I have setup a database named="number_entry" with a table named="tbl_number" and 3 fields 1) "id" = INT NULL AUTO_INCREMENT PRIMARY KEY 2) "number" = TEXT 3) "group" = TEXT. I have made a connection to the DB fine. I am just not sure how to use the select statement in conjunction with the form so when a user enters a number it determines if it matches the number in the "number" field of the database. I hope I gave enough information. If someone could graciously help me that would be great.

leatherback

Hi Garrick,

Make sure you run your HTML through a validator. Form elements need a name too:

When you submit the form, the variable $_POST['usernumber'] will contain the submitted value (Assuming you use the form method="post").

You can then use this i the query:

$usernumber = mysql_escape_real_string($_POST['usernumber']);
$q = "select FIELD from TABLE where SOMEFIELD = ".$usernumber." ";

garrickp

Hey leatherback,
First off, thanks for your response I really appreciate it. I realized my form was all messed up after I posted it and have since fixed it. Here is what I have come up with since I posted this, ( this is my complete code for the page )

<?php 
// connect to db
$conn = mysql_connect('localhost','lotteryuser','password') or trigger_error("SQL", E_USER_ERROR); 
$db = mysql_select_db('lottery',$conn) or trigger_error("SQL", E_USER_ERROR); 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
</head>

<body>
<?php
$number = mysql_real_escape_string($_GET['number'], $conn);

$queryres = "Select lottonumber FROM lottery WHERE lottonumber = ['number']";

if ($queryres == $number)
	echo "Congrats";
	else "Sorry";

?>
<form action="" method="get">
	<label name="enternumber" id="enternumber" value="Please enter number">Please enter number:</label>
    <input type="text" name="lottonumber" id="lottonumber" />
    <input type="submit" id="CehckResults" value="CheckResults"/>
</form>
</body>
</html>

<?php

mysql_close($conn);	
?>

I have a couple of question about the code that you posted. I understand the $usernumber variable but I don't understand what ['usernumber'] is from/for? Is it the name I will give the input field the user will type the number into? Also where is the $q variable from?For the form I am using method="get". I just assuming this is correct since I am not posting anything. I hope that I am not confusing you, my ultimate goal is for a user to enter a number in the in the input field. Once they click the submit button it will check the DB to see if that number matches any number in the specified field within the DB. If the number exists then it will echo a Congrats message. If it doesn't then it will echo a Sorry message.

Again I truly appreciate your help in this.

leatherback

hi,

The usernumber is the name I gave to the field in the form. In your case it is lottonumber

$q is the variable I used, where you use: $queryres

I personally always use post for the forms.

You seem to mix things up a little. Based on the form you just posted:

// You expect a number to be submitted you can check for it being numeric:
// If the submitted value is a number, it will use that, otherwise number = 0
$number = is_numeric($_GET['lottonumber']) ? $_GET['lottonumber'] : 0;

// Create the query
$queryres = "Select lottonumber FROM lottery WHERE lottonumber = ".$number;

$res = mysql_query($queryres) or die(mysql_error());

// if more then 0 rows are found
if(mysql_num_rows($res) > 0)
  {
  echo "Congrats";
  }
else
  {
  else "Sorry";
  }

garrickp

Again I really appreciate your help. I am now get this error message when I load the page: Undefined index: lottonumber in C:\wamp\www\winningnumber\index.html.php on line 6

Below is my code:

<?php
// connect to db
$conn = mysql_connect('localhost','lotteryuser','password') or trigger_error("SQL", E_USER_ERROR); 
$db = mysql_select_db('lottery',$conn) or trigger_error("SQL", E_USER_ERROR); 

$lottonumber = is_numeric($_POST['lottonumber']) ? $_POST['lottonumber'] : 0;
$queryres = "SELECT lottonumber FROM lottery WHERE lottonumber = ".$lottonumber;

$res = mysql_query($queryres) or die(mysql_error());

if(mysql_num_rows($res) > 0)
  {
  echo "Congrats";
  }
else
  {
  echo "Sorry";
  }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
</head>

<body>
<form id="lottonumberform" name="lottonumberform" method="post" action="">Please enter number:<input type="text" name="lottonumber" id="lottonumber" />
<input type="submit" name="CheckResults" id="CheckResults" value="Check Results" />
</form>
</body>
</html>

Derokorian

$lottonumber = is_numeric($_POST['lottonumber']) ? $_POST['lottonumber'] : 0;

$lottonumber = isset($_POST['lottonumber']) && is_numeric($_POST['lottonumber']) ? $_POST['lottonumber'] : 0;

garrickp

Awesome, that fixed the error message. But now when I load the page I get this: [ATTACH]4363[/ATTACH]

Basically what I want to happen, is when a user types a number in the the text box and hits submits. It check the data base for matching a matching number. If there is a match it returns a Congrats message, if there is no match it returns a sorry message.

So when I load the page now with out having typed any number in, it automatically gives a Sorry message. I also tried typing in a number that I put in the database and it doesn't return the Congrats message.

This is driving me crazy...... 🙂 I really do appreciate everyones help. Again I am very new at this, but I am very driven to learn as much as I can.

garrickp

Well I got the script to work, but when I load the page. It automatically shows a sorry message. How can I get rid of that until they type the wrong number in?

garrickp;10986340 wrote:
Awesome, that fixed the error message. But now when I load the page I get this: [ATTACH]4363[/ATTACH]

Basically what I want to happen, is when a user types a number in the the text box and hits submits. It check the data base for matching a matching number. If there is a match it returns a Congrats message, if there is no match it returns a sorry message.

So when I load the page now with out having typed any number in, it automatically gives a Sorry message. I also tried typing in a number that I put in the database and it doesn't return the Congrats message.

This is driving me crazy...... 🙂 I really do appreciate everyones help. Again I am very new at this, but I am very driven to learn as much as I can.

Derokorian

<?php
if( isset($_POST['lottonumber']) ) {
   // connect to db
   $conn = mysql_connect('localhost','lotteryuser','password') or trigger_error("SQL", E_USER_ERROR); 
   $db = mysql_select_db('lottery',$conn) or trigger_error("SQL", E_USER_ERROR); 

   $lottonumber = is_numeric($_POST['lottonumber']) ? $_POST['lottonumber'] : 0;
   $queryres = "SELECT lottonumber FROM lottery WHERE lottonumber = ".$lottonumber;

   $res = mysql_query($queryres) or die(mysql_error());

   if(mysql_num_rows($res) > 0)
     {
     echo "Congrats";
     }
   else
     {
     echo "Sorry";
     }
}
?>
<!-- Continue HTML form here //-->

garrickp

Thank you guys so much for your help on this, it is now working great.