Query does not return results.

[Angelojoseph17]

Hi I have a query where it returns a few fields based on the location. I created a class for the function and an index page. It does not return any values. Can someone please advise/

 

THE CLASS

<?php

/****************************************

* 

* WIP Progress Class

*



* 

****************************************/



class CHWIPProgress {



	var $conn;



	// Constructor, connect to the database

	public function __construct() {

		require_once "/var/www/reporting/settings.php";

		define("DAY", 86400);

		if(!$this->conn = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD)) die(mysql_error());

		if(!mysql_select_db(DB_DATABASE_NAME, $this->conn)) die(mysql_error());

	}



	public function ListWIPOnLocation($location) {

	$sql = "SELECT 

`ProgressPoint.PPDescription` AS Description ,`Bundle.WorksOrder` AS WorksOrder, `Bundle.BundleNumber` AS Number, `Bundle.BundleReference` AS Reference,`TWOrder.DueDate` AS Duedate		

		FROM `TWOrder`,`Bundle`,`ProgressPoint`

                        WHERE `Bundle.CurrentProgressPoint`=`ProgressPoint.PPNumber`

	 	AND `TWOrder.Colour=Bundle.Colour`

		AND `TWOrder.Size=Bundle.Size`

                        AND `TWOrder.WorksOrderNumber`=`Bundle.WorksOrder`

                        AND `ProgressPoint.PPDescription` LIKE '" . $location . "%'

		ORDER BY TWOrder.DueDate DESC";

		mysql_select_db(DB_DATABASE_NAME, $this->conn);

		$result = mysql_query($sql, $this->conn);
                        echo $sql; 

		while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

			$return[] = $row;

		}

		return $return;



	}





}

?>

 

 

 

The index page

<?php



// First of all initialise the user and check for permissions

require_once "/var/www/users/user.php";

$user = new CHUser(7);

       



// Initialise the template

require_once "/var/www/template/template.php";

$template = new CHTemplate();



// And create a cid object

        require_once "/var/www/WIPProgress/DisplayWIPOnLocation.php";

$WIPProgress= new CHWIPProgress();



        $content = "Check WIP Status on Location <br>";



        $content = "<form action='index.php' method='get' name ='location'>

                    <select id='location' >

		    <option>Skin Room</option>

                    <option>Clicking</option>

                    <option>Kettering</option>

	    <option>Closing</option>

                    <option>Rushden</option>

                    <option>Assembly</option>

                    <option>Lasting</option>

                    <option>Making</option>

                    <option>Finishing</option>

                    <option>Shoe Room</option> 

                     </select>

                     <input type='submit' />

                     </form>"; 




      $wip = $WIPProgress->ListWIPOnLocation($_GET['location']);
      
     // Now show the details

	$content .= "<h2>Detail</h2>

	<table>

		<tr>

			<th>PPDescription</th>

			<th>Works Order</th>                 

			<th>Bundle Number</th>

			<th>Bundle Reference</th>
                                <th>Due Date</th>  

		</tr>";

	foreach($wip as $x) {

		$content .= "<tr>

					<td>" . $x['Description'] . "</td>

					<td>" . $x['WorksOrder'] . "</td>

					<td>" . $x['Number'] . "</td>
					<td>" . $x['Reference'] . "</td>
                                                <td>" . $x['DueDate'] . "</td>

				</tr>";

	}






$template->SetTag("content", $content);

echo $template->Display();





?>

 

 

thank you

[GingerRobot]

When debugging unexpected results from a query, the following two thing should nearly always be your first steps:

 

1.) Execute the query in PHPMyAdmin/the MySQL console. You may have to 'echo' your query if you're using any variables in it, so that you can execute the exact same query your script is.

2.) Add an "or die" statement to mysql_query() ( or better, use trigger_error() ). E.g. mysql_query($sql) or die (mysql_error()); See my tutorial for more details.

 

Once you've verified that your query returns the correct results and no errors are being generated, you can then start to point your finger at your code and investigate the problems.