Need help w/ php sql error!

ShadowIce

Hi all. I'm having a bit of a problem. The code is returning this as an error:

Notice: Undefined variable: warning in C:\myxampp\htdocs\detect\index.php on line 56

Notice: Undefined variable: banned in C:\myxampp\htdocs\detect\index.php on line 56
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 ' FROM users0001' at line 1

this code does the error:

$query = "SELECT $warning, $banned FROM $tbl";
//var_dump($query."<br>\n");
$result = mysql_query($query) or die(mysql_error());

//now lets go through every row returned.
while($row = mysql_fetch_assoc($result)){

$warning = $row[$warning1];
$banned = $row[$banned1];

$rows = mysql_num_rows($result);

echo "$rows rows returned!<br />";

echo "Warning: $warning<br />";
echo "Banned: $banned<br />";

if(isset($warning)){

here's the entire code:

<head><title>Metal Detecting</title></head>
<body onload="document.forms.test.s1.disabled = true;" oncontextmenu="return false;">
<style type="text/css">
a:hover{Color: #FFFFFF;text-decoration: none;}
a:link{Color: #FFFFFF;text-decoration: underline;}
a:visited{Color: #FF0000;text-decoration: underline;}
BODY{
Color: #FFFFFF;
background-image:url('imgs1/S7301474.jpg');
background-Position: Center;
background-attachment: fixed;
background-repeat: no-repeat;
}
.input {
padding-right: 1px; padding-left: 1px; padding-bottom: 1px; margin: 4px 0px 5px 8px; font: 11px/14px Arial, Helvetica, sans-serif; color: #5a698b; padding-top: 1px
}
#title {
font: bold 11px/18px Arial, Helvetica, sans-serif; text-transform: uppercase; width: 330px; color: #5a698b; padding-top: 10px; padding-bottom:10px; letter-spacing: 2px; height: 26px; text-align: center
}
#firstname{
margin 0.5em;
}
#lastname{
margin 0.5em;
}
#email{
margin 0.5em;
}
</style>
<table style="width: 1024; height: 50;" border="1" cellpadding="0" cellspacing="0"><td>
<a href="./itemsubmit.php">Submit Items</a><br/>
</td></table>
<script src="script01.js">
</script>
<?

error_reporting(E_ALL);
ini_set('display_errors', 1);

$conn = "localhost";
$user = "myuser";
$pass = "mypass";
$dbname = "metaldetect01";
$tbl = "users0001";
$warning1 = "`warning`";
$banned1 = "`banned`";

$con = mysql_connect($conn,$user,$pass);
if (!$con)
  {
  die('Could not connect to database: "' . $dbname . '" because ' . mysql_error());
  }

mysql_select_db($dbname, $con);

$query = "SELECT $warning, $banned FROM $tbl";
//var_dump($query."<br>\n");
$result = mysql_query($query) or die(mysql_error());

//now lets go through every row returned.
while($row = mysql_fetch_assoc($result)){

$warning = $row[$warning1];
$banned = $row[$banned1];

$rows = mysql_num_rows($result);

echo "$rows rows returned!<br />";

echo "Warning: $warning<br />";
echo "Banned: $banned<br />";

if(isset($warning)){
echo "Please contact us!<br /><br />\n";
echo "*Please note you can submit the form ONLY once. Any double form submissions will be deleted.<br />\n";
echo "*You will be emailed depending on your statement whether or not you've been approved to the group!<br />\n";
echo "*You MUST speak FLUENT english<br />\n";
echo "<center><font Color=\"#00FF00\">You have been warned!</font></center>\n";
echo "<form name=\"test\" id=\"test\" method=\"POST\" onsubmit=\"return checkdata(this), emailCheck(this.email.value), checkCheckBox(this)\" action=\"send.php\">\n";
echo "<div id = \"div01\" style=\"width: 100; height: 25;\">\n";
echo "  Phone/Cell number: <input name=\"requirednumber\" id=\"number\" type=\"text\" />\n";
echo "  Firstname: <input name=\"requiredfirstname\" id=\"firstname\" type=\"text\" />\n";
echo "  Lastname: <input name=\"requiredlastname\" id=\"lastname\" type=\"text\" />\n";
echo "  Email: <input name=\"requiredemail\" id=\"email\" type=\"text\" /><br /><br />\n";
echo "</div>\n";
echo "  Tell us what kind of metal detector you have, what you will contribute to the community, and why you want on the team to begin with, you must give at least 2 sentences, and maximum 1 paragraph.<br />\n";
echo "  Be as DETAILED as possible!<br />\n";
echo "<H4>Your statement: </H4>\n";
echo "  <textarea onKeyDown=\"textCounter(this.form.statement,this.form.counter,max);\" onKeyUp=\"textCounter(this.form.statement,this.form.counter,max);\" name=\"requiredstatement\" id=\"statement\" rows=\"15\" cols=\"40\"></textarea><br />\n";
echo " Characters left: <input readonly=\"readonly\" value=\"384\" size=3 maxlength=3 type=\"text\" name=\"counter\" id=\"counter\"><br/><br />\n";
echo "<textarea readonly=\"readonly\" name=\"license\" cols=\"40\" rows=\"15\" id=\"license\">I agree to give 20% of my earnings to the club when I have more than 30% of earnings and I also agree that I will report what I find each required day even if I don't find anything on the item submit page</textarea><br/>\n";
echo "<input onclick=\"this.form.s1.disabled=! this.checked;\" name=\"agree\" id=\"agree\" type=\"checkbox\"> I have read & agree to the above<br/>\n";
echo "  <input name=\"s1\" id=\"s1\" value=\"Submit\" type=\"submit\" />&nbsp;<input type=\"reset\" name=\"rset\" value=\"Reset\" /><br/>\n";
echo "</form>\n";
}else if( isset($banned)){
echo "<font Color=\"#FF0000\">Sorry, but you have been banned!</font>";
}else if(! isset($warning) || isset($banned)){
echo "Please contact us!<br /><br />\n";
echo "*Please note you can submit the form ONLY once. Any double form submissions will be deleted.<br />\n";
echo "*You will be emailed depending on your statement whether or not you've been approved to the group!<br />\n";
echo "*You MUST speak FLUENT english<br />\n";
echo "<form name=\"test\" id=\"test\" method=\"POST\" onsubmit=\"return checkdata(this), emailCheck(this.email.value), checkCheckBox(this)\" action=\"send.php\">\n";
echo "<div id = \"div01\" style=\"width: 100; height: 25;\">";
echo "  Phone/Cell number: <input name=\"requirednumber\" id=\"number\" type=\"text\" />\n";
echo "  Firstname: <input name=\"requiredfirstname\" id=\"firstname\" type=\"text\" />\n";
echo "  Lastname: <input name=\"requiredlastname\" id=\"lastname\" type=\"text\" />\n";
echo "  Email: <input name=\"requiredemail\" id=\"email\" type=\"text\" /><br /><br />\n";
echo "</div>\n";
echo "  Tell us what kind of metal detector you have, what you will contribute to the community, and why you want on the team to begin with, you must give at least 2 sentences, and maximum 1 paragraph.<br />\n";
echo "  Be as DETAILED as possible!<br />\n";
echo "<H4>Your statement: </H4>\n";
echo "  <textarea onKeyDown=\"textCounter(this.form.statement,this.form.counter,max);\" onKeyUp=\"textCounter(this.form.statement,this.form.counter,max);\" name=\"requiredstatement\" id=\"statement\" rows=\"15\" cols=\"40\"></textarea><br />\n";
echo " Characters left: <input readonly=\"readonly\" value=\"384\" size=3 maxlength=3 type=\"text\" name=\"counter\" id=\"counter\"><br/><br />\n";
echo "<textarea readonly=\"readonly\" name=\"license\" cols=\"40\" rows=\"15\" id=\"license\">I agree to give 20% of my earnings to the club when I have more than 30% of earnings and I also agree that I will report what I find each required day even if I don't find anything on the item submit page</textarea><br/>\n";
echo "<input onclick=\"this.form.s1.disabled=! this.checked;\" name=\"agree\" id=\"agree\" type=\"checkbox\"> I have read & agree to the above<br/>\n";
echo "  <input name=\"s1\" id=\"s1\" value=\"Submit\" type=\"submit\" />&nbsp;<input type=\"reset\" name=\"rset\" value=\"Reset\" /><br/>\n";
echo "</form>\n";
}
}
?>
</body>
</html>

etully

I think you mean for your query to say:

$query = "SELECT $warning1, $banned1 FROM $tbl";
instead of
$query = "SELECT $warning, $banned FROM $tbl";

ShadowIce

Ok. it works w/ no errors. ALMOST.

Now its not displaying ANYTHING..

code:

<?

$conn = "localhost";
$user = "root";
$pass = "Gen0sis";
$dbname = "metaldetect01";
$tbl = "users0001";
$warning1 = "`warning`";
$banned1 = "`banned`";

$con = mysql_connect($conn,$user,$pass);
if (!$con)
  {
  die('Could not connect to database: "' . $dbname . '" because ' . mysql_error());
  }

mysql_select_db($dbname, $con);

$query = "SELECT $warning1, $banned1 FROM $tbl"; 
//var_dump($query."<br>\n"); 
$result = mysql_query($query) or die(mysql_error()); 

error_reporting(E_ALL);
ini_set('display_errors', 1);

//now lets go through every row returned. 
while($row = mysql_fetch_assoc($result)){ 

$warning = $row[$warning1]; 
$banned = $row[$banned1]; 

$rows = mysql_num_rows($result); 

echo "$rows rows returned!<br />"; 

echo "Warning: $warning<br />"; 
echo "Banned: $banned<br />"; 

if(isset($warning)){

etully

Is there anything in the users0001 table?

ShadowIce

yes.

here:

  id int(11)   No None auto_increment               

  number varchar(16) latin1_swedish_ci  No                 

  Firstname varchar(32) latin1_swedish_ci  No                 

  Lastname varchar(32) latin1_swedish_ci  No                 

  email varchar(50) latin1_swedish_ci  No                 

  statement varchar(255) latin1_swedish_ci  No                 

  warning varchar(1) latin1_swedish_ci  No 0                

  banned varchar(1) latin1_swedish_ci  No 0

ShadowIce

echo "warning:".$warning."<br>\n";
echo "banned:".$banned."<br>\n";

return this:

warning: 
banned:

etully

Well, a table is made up of fields like firstname, lastname, email, warning, and banned. Then, inside a table are records which would be things like "Sally" as someone's first name and "Smith" as their last name.

It appears that your table doesn't have any records (data) in it even though it does have fields.

ShadowIce

um, i defaulted warning and banned to 0..

thats impossible..

what should i do then?

etully

You are missing a fundemental understanding of how databases work.

You can build a table that has NAME, ADDRESS, CITY, STATE, and AGE.

Once you do that, you have a table with ZERO records in it. It's your responsibility to put data in it. So you might add some records like this:

John, Main St, Hartford, CT, 36
Betty, Route 4, Albany, NY, 40
Chris, 23 Bee Road, San Francisco, CA, 78

Now you have a table with three records. And then you can perform select statements that will extract that data from the database.

Just defaulting "warning" to 0 means that whenever you add a record to the table and don't define a value for the warning field, then the system will automatically use "0" for that record. But... You have not added a record to your table yet. So there is NO data in your table. If there is no data, then there is no data to have defaulted to "0".

A table by itself has no data. You need to add records to it.

ShadowIce

ok. well if u can tell me where to fill in the value for "0" for both warning and banned, and what code to use for it, then i think i figured this out..

<?php
$conn = "localhost";
$user = "myuser";
$pass = "mypss";
$dbname = "metaldetect01";
$tbl = "users0001";
$con = mysql_connect($conn,$user,$pass);
if (!$con)
  {
  die('Could not connect to database "'.$dbname. '" because ' . mysql_error());
  }

// Drop database
mysql_query("DROP DATABASE IF EXISTS $dbname");

// Create database
if (mysql_query("CREATE DATABASE $dbname"))
  {
  echo "Database \"".$dbname."\" created";
  }
else
  {
  echo "Error creating database: \"".$dbname."\"\nError msg: " . mysql_error();
  }

// Create table
mysql_select_db($dbname, $con);
$sql = "CREATE TABLE $tbl
(
id int(11) NOT NULL auto_increment, 
number varchar(16) NOT NULL default '', 
Firstname varchar(32) NOT NULL default '', 
Lastname varchar(32) NOT NULL default '', 
email varchar(50) NOT NULL default '', 
statement varchar(255) NOT NULL default '', 
warning varchar(1) NOT NULL default '0', 
banned varchar(1) NOT NULL default '0', 
PRIMARY KEY (id)
)TYPE=MyISAM;";

// Execute query
mysql_query($sql,$con);

mysql_close($con);
?>

johanafm

There are lots of problems. If the database exist you remove it. Then you create it again. Then you create your table. Where's the INSERT query/queries?

Also, even if you had anything in it to begin with:
First you

SELECT `warning` ...

And then

$array["`warning`"];	// No such key

$array["warning"];		// This one does exist though

And finally, you do not want your mysql_num_rows inside the while loop. Getting that once should be more than enough.

ShadowIce

ok, that DID not help..