Page 1 of 1
***FIXED***trouble with select***FIXED***
Posted: Sat Nov 06, 2010 12:52 pm
by Obadiah
ok...lets say i have these 5 fields in my table:
num(auto_incrementing primary key)
area(text)
month(text) <--filled with a variable displaying the month submitted
percent(text)
stat(text)
what I want to do is do a check to see if a record already for a specific area exist for the present month. If no record for the specific area exist for the present month allow the user data to be posted to the database...if one does exist say "data for area already exist for this month" and post nothing.
what i have tried is this
Code: Select all
$query = mysql_query("SELECT Month, Area FROM data WHERE Month ='$DMonth' AND WHERE Area ='$areaSelected'");
$numrows = mysql_num_rows($query);
if ($numrows!=0)
{
while ($row = mysql_fetch_assoc($query))
{
$dbMonth = $row['month'];
$dbArea = $row['area'];
}
if ($DMonth==$dbMonth&&$areaReported==$dbArea)
{
mysql_query("INSERT INTO $table_name (Month, Area, percent, stat)
values ('{$Dmonth}',
'{$areaReported}',
'{$percent}',
'{$stat}')");
}
else {
$display_block.="<p style=\"color:red;\">A Report for this $areaReported has already been submitted for this Month</p>";
}
i get the error
mysql_num_rows() expects parameter 1 to be resource, boolean given
Re: trouble with select
Posted: Sat Nov 06, 2010 2:00 pm
by VladSun
Code: Select all
$query = mysql_query("SELECT Month, Area FROM data WHERE Month ='$DMonth' AND WHERE Area ='$areaSelected'") or die(mysql_error());
Re: trouble with select
Posted: Sat Nov 06, 2010 8:00 pm
by Obadiah
ok this is killin me its not working and when it works it works backwards...whats happening is when I use $DArea anywhere in the Query it doesnt work, If I choose a different Area than one in the database It executes the Else Statement(not supposed to do that) and it post data from the same Month, Year and Area to the database...once again what im trying to do is not allow any information from an Area "$DArea" in the database unless the Month or the Year is different.
In other words If I fill out information from area "Gym" on November("DMonth") of 2010("DYear") the next time i will be allowed to enter data on it is next month. And if you were wondering Dmonth and DYear are variables tied to the time() function abd it does pass the correct month and time to the database.
Code: Select all
$DArea = $areaReported;
$query = mysql_query("SELECT * FROM $table_name WHERE Month ='$DMonth' AND Year= '$DYear' AND Area = '$DArea'") or die(mysql_error());
$numrows = mysql_num_rows($query);
if ($numrows!=0)
{
while ($row = mysql_fetch_assoc($query))
{
$dbMonth = $row['Month'];
$dbYear = $row['Year'];
$dbArea = $row['Area'];
}
if ($DMonth==$dbMonth&&$DYear==$dbYear&&$areaReported==$dbArea)
{
mysql_query("INSERT INTO $table_name (user_id, Date, Month, Year, Area, percent1, stat1)
values ('{$user_id}',
'{$realdate}',
'{$DMonth}',
'{$DYear}',
'{$DArea}',
'{$percent1}',
'{$stat1}')");
// defining the output
include("output.inc");
if ($total == "1"){
$output = $output1;
}
else if ($total == "2"){
$output = $output2;
}
else if ($total == "3"){
$output = $output3;
}
else if ($total == "4"){
$output = $output4;
}
else if ($total == "5"){
$output = $output5;
}
else if ($total == "6"){
$output = $output6;
}
else if ($total == "7"){
$output = $output7;
}
else if ($total == "8"){
$output = $output8;
}
else if ($total == "9"){
$output = $output9;
}
else if ($total == "10"){
$output = $output10;
}
}
else
echo "<p style=\"color:red;\">A Report for this $areaReported has already been submitted for this Month</p>";
}
Re: trouble with select
Posted: Sun Nov 07, 2010 2:27 am
by VladSun
1. Print your actual query.
2. Use `` to surround all column/table names. E.g.
Code: Select all
SELECT `YEAR`, .... FROM `table` WHERE `YEAR`=...
Re: trouble with select
Posted: Sun Nov 07, 2010 5:34 am
by Obadiah
VladSun wrote:1. Print your actual query.
2. Use `` to surround all column/table names. E.g.
Code: Select all
SELECT `YEAR`, .... FROM `table` WHERE `YEAR`=...
if im correct you wanted me to try this
Code: Select all
mysql_query("SELECT 'Month', 'Year', 'Area' FROM 'data' WHERE 'Month' ='$DMonth' AND 'Year'='$DYear' AND 'Area' = '$areaReported'")
When I did that it says
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 ''data' WHERE 'Month' ='November' AND 'Year'='2010' AND 'Area' = 'BronchLab'' at line 1
am I going in the right direction here?
Re: trouble with select
Posted: Sun Nov 07, 2010 11:14 am
by califdon
Vladsun advised you to use "back-ticks" (the character `), not the apostrophe/single-quote (the character '). Use single back-ticks around table names and field names, because you have used names like Date, Month and Year, which are reserved words, confusing MySQL. Also, Vladsun advised you to use the "or die(mysql_error())" after your mysql_query() so that you will receive an error message which will show what your problem is.
Re: trouble with select
Posted: Sun Nov 07, 2010 12:11 pm
by Obadiah
The (`) actually worked...but now the output is not printing out and the info is not going to the database... do I not have my arguments in the right place?
Code: Select all
$query = mysql_query("SELECT * FROM `$table_name` WHERE `Month` ='$DMonth' AND `Year`= '$DYear' AND `Area` = '$DArea'") or die(mysql_error());
$numrows = mysql_num_rows($query);
if ($numrows!=0)
{
while ($row = mysql_fetch_assoc($query))
{
$dbMonth = $row['Month'];
$dbYear = $row['Year'];
$dbArea = $row['Area'];
}
if ($DMonth==$dbMonth&&$DYear==$dbYear&&$areaReported==$dbArea)
{
mysql_query("INSERT INTO $table_name (user_id, Date, Month, Year, Area, percent1, stat1)
values ('{$user_id}',
'{$realdate}',
'{$DMonth}',
'{$DYear}',
'{$DArea}',
'{$percent1}',
'{$stat1}')");
// defining the output
include("output.inc");
if ($total == "1"){
$output = $output1;
}
else if ($total == "2"){
$output = $output2;
}
else if ($total == "3"){
$output = $output3;
}
else if ($total == "4"){
$output = $output4;
}
else if ($total == "5"){
$output = $output5;
}
else if ($total == "6"){
$output = $output6;
}
else if ($total == "7"){
$output = $output7;
}
else if ($total == "8"){
$output = $output8;
}
else if ($total == "9"){
$output = $output9;
}
else if ($total == "10"){
$output = $output10;
}
}
else
echo "<p style=\"color:red;\">A Report for this $areaReported has already been submitted for this Month</p>";
}
Re: trouble with select
Posted: Sun Nov 07, 2010 12:38 pm
by VladSun
You should do the same with the INSERT statement.
Re: trouble with select
Posted: Sun Nov 07, 2010 1:56 pm
by Obadiah
Code: Select all
$query = mysql_query("SELECT `Month`, `Year`, `Area` FROM `data` WHERE `Month` ='$DMonth' AND `Year`='$DYear' AND `Area` = '$DArea' ") or die(mysql_error());
$numrows = mysql_num_rows($query);
if ($numrows!=0)
{
while ($row = mysql_fetch_assoc($query))
{
$dbMonth = $row['Month'];
$dbYear = $row['Year'];
$dbArea = $row['Area'];
}
if ($DMonth==$dbMonth&&$DYear==$dbYear&&$$DArea==$dbArea)
{
mysql_query("INSERT INTO `data` (`user_id`, `Date`, `Month`, `Year`, `Area`, `percent1`, `stat1`)
values ('{$user_id}',
'{$realdate}',
'{$DMonth}',
'{$DYear}',
'{$DArea}',
'{$percent1}',
'{$stat1}')")
or die(mysql_error());
// defining the output
include("output.inc");
if ($total == "1"){
$output = $output1;
}
else if ($total == "2"){
$output = $output2;
}
else if ($total == "3"){
$output = $output3;
}
else if ($total == "4"){
$output = $output4;
}
else if ($total == "5"){
$output = $output5;
}
else if ($total == "6"){
$output = $output6;
}
else if ($total == "7"){
$output = $output7;
}
else if ($total == "8"){
$output = $output8;
}
else if ($total == "9"){
$output = $output9;
}
else if ($total == "10"){
$output = $output10;
}
}
else
echo "<p style=\"color:red;\">A Report for this $areaReported has already been submitted for this Month</p>";
}
it actually stops the information from going into the database and tells you that your not able to post it because the month is the same but it doesnt show the output when the statement is false
Re: trouble with select
Posted: Sun Nov 07, 2010 3:13 pm
by VladSun
Debug it.
Re: trouble with select
Posted: Sun Nov 07, 2010 5:52 pm
by McInfo
I think some of your logic is incorrect. This is pseudo-code of what you have now:
- Attempt to select some rows.
- If there is a positive or negative number of rows (number of rows is not zero),
- Fetch all the selected rows and remember the month, year, and area of the last row.
- If the month, year, and area of the last row are the same as what was selected in the first query,
- Try to insert another row with that same data.
- Include output.inc.
- Do stuff with $total, $output, etc.
- If any of month, year, and area are different from what was selected in the first query,
- Say a report has been submitted already.
- (Implied) If there were zero rows selected, do nothing.
Anyway, try something simpler. If your table doesn't already have a unique index that combines area, year, and month, create one. Then try this.
Code: Select all
// MySQL connection is assumed.
// Sample inputs.
$area = 'Gym';
$year = '2010';
$month = 'November';
// Builds a safe query.
$query = sprintf(
"INSERT INTO `data` (`area`, `year`, `month`, `percent`, `stat`) VALUES ('%s', '%s', '%s', '%s', '%s')",
mysql_real_escape_string($area),
mysql_real_escape_string($year),
mysql_real_escape_string($month),
mysql_real_escape_string('100%'),
mysql_real_escape_string('none')
);
// Runs the query and tests the return value.
if (mysql_query($query)) {
// The return value is equivalent to true.
echo "A new entry was inserted.\n";
} else {
// The return value is equivalent to false.
// Tests the error code. 1062 means duplicate entry.
if (mysql_errno() == 1062) {
// Prints an error message.
printf(
"An entry already exists for Area: \"%s\" + Year: \"%s\" + Month: \"%s\".\n",
$area,
$year,
$month
);
}
// Prints another error message so something will be shown
// even if error 1062 is not the reason we are here.
echo "Nothing was inserted.\n";
}
Then (or maybe first), learn about MySQL data types. The text type is too bloated and, as far as I can see, inappropriate for the fields in your table. Try smaller varchar and integer types.
***FIXED***Re: trouble with select***FIXED***
Posted: Sun Nov 07, 2010 7:08 pm
by Obadiah
Code: Select all
$query = mysql_query("SELECT `Month`, `Year`, `Area` FROM `$table_name` WHERE `Month` ='$DMonth' AND `Year`='$DYear' AND `Area` = '$areaReported'") or die(mysql_error());
$numrows = mysql_num_rows($query);
if ($numrows != 0){
echo "<p style=\"color:red;\">A Report for this $areaReported has already been submitted for this Month</p>";
}
else if ($numrows < 1 )
{
//mysql insert query
}
that fixed it....thanks devnet!!!
