[SOLVED] PHP MySql variable in SELECT

[mdannatt]

Ah! Hello from an absolute noob!

Much as I have battled to figure out how to solve my problem, I have had no success. I can HEAR all the master coders groaning collectively and rolling your eyeballs, but I really dont know where else to turn to. I throw myself before your greatness..

I have built a mySql database for a client. I have defined the relationships. Now I am trying to code the summary report in PHP. Thought of using one one those reporting package, but the nature of the calculations doesn't allow me to do that.

Here is the basic idea. The client uses a table called report to input 3 fields: the name of the report, the starting date and the ending date. A PHP script displays the list of reports in a dropdown list and the user then chooses the report he wants:

***************************************************************

echo "<form action=\"DBconnect.php\" method=\"GET\">

<table border=0 cellpadding=5 align=left><tr><td><b>Select a Report</b><br>";

 

$res=mysql_query("select * from report ORDER BY reportname");

if(mysql_num_rows($res)==0){

echo "there is no data in table..";

} else {

echo "<select name=\"reportname\" id=\"reportname\"><option value=\"ALL\">ALL</option>";

for($i=0;$i<mysql_num_rows($res);$i++) {

$row=mysql_fetch_assoc($res);

echo"<option value=$row[report_ID]";

if($Var==$row[report_ID])

echo " selected";

echo ">$row[reportname]</option>";

}

 

echo "</select>";

}

 

echo "</td><td align=\"left\"><input type=\"submit\" value=\"Search Now\" />

</td></tr></table></form><br>";

**************************************************************

So far so far, so good. Now I return the starting date (sdate) from the requested report, using fetch_assoc(), so that I dont return the resource ID, but the actual starting date. I echo this just to make sure I have got the right data:

**********************************************************************

//THIS TAKES THE REPORT NAME WE HAVE SELECTED, QUERIES THE DATABASE FOR THE REQUIRED INFO AND OUTPUTS OUR RESULT

 

$query = "SELECT `sdate` FROM `report` WHERE `report_ID` LIKE '$Var' ORDER BY `report_ID` ASC LIMIT 0, 30 ";

$result = mysql_query($query) or die("Error: " . mysql_error());

 

if(mysql_num_rows($result) == 0){

echo("No reports match your currently selected criteria. Please try another selection!");

} ELSE {

 

// Output- we use 'fetch_assoc' so that we dont just return the resource ID#

 

echo "<br />";

}

if (mysql_num_rows($result) >= '1'){

while ($row = mysql_fetch_assoc($result)){

echo $row['sdate']; //to print out the value of column 'start date record

$startdate=$row['sdate'];

}

}else{

echo 'No records found.';

//THIS TAKES THE REPORT NAME WE HAVE SELECTED, QUERIES THE DATABASE FOR THE REQUIRED INFO AND OUTPUTS OUR RESULT

 

$query = "SELECT `sdate` FROM `report` WHERE `report_ID` LIKE '$Var' ORDER BY `report_ID` ASC LIMIT 0, 30 ";

$result = mysql_query($query) or die("Error: " . mysql_error());

 

if(mysql_num_rows($result) == 0){

echo("No reports match your currently selected criteria. Please try another selection!");

} ELSE {

 

// Output- we use 'fetch_assoc' so that we dont just return the resource ID#

 

echo "<br />";

}

if (mysql_num_rows($result) >= '1'){

while ($row = mysql_fetch_assoc($result)){

echo $row['sdate']; //to print out the value of column 'start date record

$startdate=$row['sdate'];

}

}else{

echo 'No records found.';

***************************************************************

Again, so problems, this returns the starting date and echos it. Now it go's a little pear shaped. I want to use SDATE in a new query which will build the report. I unsure how to use SDATE as a variable. Here is my query to the database:

******************************************************************

$query = "SELECT `supplierinfo`.`procurementdate`, `supplierinfo`.`suppliername`, `supplierinfo`.`procurementspend`'

        . ' FROM `supplierinfo`'

      . ' WHERE `supplierinfo`.`procurementdate` >= "$sdate"'

        . ' ORDER BY `supplierinfo`.`suppliername` ASC, `supplierinfo`.`procurementspend` DESC";

$result = mysql_query($query) or die("Error: " . mysql_error());

if(mysql_num_rows($result) == 0){

echo("No reports match your currently selected criteria. Please try another selection!");

} ELSE {

 

echo "<br />";

}

if (mysql_num_rows($result) >= '1'){

while ($row = mysql_fetch_assoc($result)){

echo $row['suppliername']; //to print out the value of column 'start date record

}

}else{

echo 'No records found.';

*********************************************************************

SO there you have it. I need to use sdate which i called using fetch_assoc in my query to mySql. I also have and ending date 'edate'. The whole point of this is to build a report using only date that falls between the two dates.

 

Any help much appreciated, I am going bald trying to solve this, but I really don't know PHP.

 

 

 

 

[hoogie]

Maybe I'm missing something, but don't you want to be using the variable $startdate in your query?  I don't see a place where you set the $sdate variable.

[Malevolence]

I'm not awake enough to go trhough this but... You're welcome to dump your problems on us lot, it's really what this place is for We all start somewhere with everything

[hoogie]

One other thing you may or may not be aware of is that to query a date range, you can use the following:

 

WHERE supplierinfo.procurmentdate BETWEEN $sdate AND $edate

 

Assuming, of course, that you have set $sdate and $edate prior.