gamerzfuse

Thanks loads guys, that was going to be my second question Stryves. Thanks again!

I have a query //Make first query to database $query = "SELECT * FROM vehicles"; $result = mysql_query($query) or die ("Couldn’t execute query."); //Set Variables $vehiclemodel = $vehicles->model->ViewValue; $query = "SELECT * FROM vehicles WHERE model='$vehiclemodel'"; $result = mysql_query($query) or die ("Couldn't execute query."); // Display results in a table echo "<tr><td colspan='5'></td></tr>"; while ($row = mysql_fetch_array($result)) { extract($row); //Table Contents $show_price = number_format($price); echo "<tr class='dataright'>\n <td colspan='5' class='dataleft'><a href='view.php?stock=$stock'> $year $model</a></td>\n <td>$odometer km </td>\n <td align='right'>$$show_price.00 </td>\n </tr>\n"; echo "<tr><td colspan='3'></td></tr>\n"; } I want to limit the results to only display # results. (ie: 10 results) Google searches for "limit query" etc haven't given me what I'm looking for. Any hints, links, etc would be much appreciated.

I used that and now I get an unexpected T_Var? Thanks though, if you could just advise me on this one last thing.. I'll be done with this site almost. Noob mistake. Left in an extra } Works great. Hopefully this fixes all my problems! Thanks for the tolerance Ken

That's what I'm trying to do. I can't seem to get it right. I take out one of the ending quotes and all my colors are fine again, but then I get an error about unexpected ;

Pretty much as you describe there. The logic is: if the variable is not empty, search using the variable. If it's not, dont add a where clause. The closest I can come is: function SqlWhere() { // Where Type return (!empty($_GET['type'])) ? "type = '". mysql_real_escape_string($_GET['type']) . "' : ""; }

function SqlWhere() { // Where Type return (!empty($_GET['type'])) ? "type = '". mysql_real_escape_string($_GET['type']) . "' : ""; } This one gives me an error of unexpected "", but if I take one away it gives me unexpected ; This post from PHP.net may be more like what I need? Although that has no return statement.. so probably not.

Ok.. googling now. function SqlWhere() { // Where Type return "(!empty($_GET['type'])) ? type= '". mysql_real_escape_string($_GET['type']) . "' : "; } I know that this may make it more difficult in the future, but this is the only thing I'll ever need to get from the URL.

I just don't know how. My if then statements were learned in QBasic. I'm googling it, but I can't seem to find how you could return an if then statement.

The old forum post solved my problem entirely, but it just introduced another one. Rather than having a 3 page post with a new problem, I decided to start over here. Situation: I have the code: $sSql = $vehicles->SelectSQL(); I edited that function to include: // Table level SQL function SqlSelect() { // Select return "SELECT * FROM `vehicles`"; } function SqlWhere() { // Where Type return "type= '". mysql_real_escape_string($_GET['type']) . "'"; //Used to say just return ""; } function SqlGroupBy() { // Group By return ""; } function SqlHaving() { // Having return ""; } function SqlOrderBy() { // Order By return ""; } Now I have a working sort on all of my pages that have a specified type (in example: http://www.example.com/inventory.php?type=car ) The problem is that if I have no type, then it lists no vehicles. How can I use an if/then statement or the empty() to make it not display a type if there is no type listed? Thanks again everyone!

OK! Last issue: I took out the whole line of code and set it back to: $sSql = $vehicles->SelectSQL(); Then I edited that function to include: // Table level SQL function SqlSelect() { // Select return "SELECT * FROM `vehicles`"; } function SqlWhere() { // Where Type return "type= '". mysql_real_escape_string($_GET['type']) . "'"; } function SqlGroupBy() { // Group By return ""; } function SqlHaving() { // Having return ""; } function SqlOrderBy() { // Order By return ""; } Now it works perfect IF I specify a type in the URL. If I navigate to just inventory.php then it won't display any records because the output of the execution is: SELECT * FROM `vehicles` WHERE type= '' ORDER BY `model` ASC Any chance I can use something like this empty() in order to avoid having that information added if there is no type specified?

The code you supplied fixed all the errors and gives me this output from the variable: SELECT * FROM `vehicles` WHERE type = 'car' ORDER BY 'model' By clicking column headers I can change the output from the variable correctly, but it doesn't actually sort the table..

Ok so.. I'm very close. I got the right output from the variable: SELECT * FROM `vehicles` WHERE type = 'car' AND ORDER BY 'make' By using the code: $sSql = (!empty($_GET['type'])) ? $vehicles->SqlSelect() . " WHERE type = '" . mysql_real_escape_string($_GET['type']) . "' AND ORDER BY '" . $_GET['order'] . "'" : $vehicles->SQL(); (thanks Ken) Now I'm still getting the errors 1) Failed to execute SQL. Error: 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 'ORDER BY 'make'' at line 1 2) Fatal error: Call to a member function fields() on a non-object in /home8/craighoo/public_html/goodwills/final/inventory.php on line 913 (Line 913 just calls on the field Status, and since it's not coming up due to the previous error, this error comes up too) Do I need an order type in my syntax too?

$sSql = (!empty($_GET['type'])) ? $vehicles->SqlSelect() . " WHERE type = '" . mysql_real_escape_string($_GET['type']) . "'" : $vehicles->SQL() "AND ORDER BY '" . $_GET['order'] . "'"; That line of code? If so, could you possibly tell me how to properly add it to the end of that URL as mine is clearly incorrect. I know this is getting annoying for you I'm sure and I hate to be a pain in the ass.. I'm just trying to learn this stuff and it's alot at once and I've read more tutorials and guides in the past week then I care to remember.

Oh.. how would I add that? Or where is it missing from (in the above code?)

You don't have to search through my site. It doesn't sort as in: On the first page, if you click a column header it sorts the table by the content of that column. As soon as you declare a type in the URL the headers stop sorting.

string(3) "car"

That's fine, I'm certainly not asking you to go through my work and make changes. I'm just asking for guidance. They are all the same file, just different sections. In Example: http://craighooghiem.com/goodwills/final/inventory.php?type=&order=model&ordertype=ASC Works perfect. http://craighooghiem.com/goodwills/final/inventory.php?type=car&order=model&ordertype=ASC Doesn't sort at all.

That made sort work on the main page, and the errors are gone, but the Sort still doesn't work on any pages such as http://craighooghiem.com/goodwills/final/inventory.php?type=car&order=make&ordertype=ASC Not sure if it help to see: // Table level SQL function SqlSelect() { // Select return "SELECT * FROM `vehicles`"; } function SqlWhere() { // Where return ""; } function SqlGroupBy() { // Group By return ""; } function SqlHaving() { // Having return ""; } function SqlOrderBy() { // Order By return ""; }

function ew_BuildSelectSql($sSelect, $sWhere, $sGroupBy, $sHaving, $sOrderBy, $sFilter, $sSort) { $sDbWhere = $sWhere; if ($sDbWhere <> "") { if ($sFilter <> "") $sDbWhere = "($sDbWhere) AND ($sFilter)"; } else { $sDbWhere = $sFilter; } $sDbOrderBy = $sOrderBy; if ($sSort <> "") $sDbOrderBy = $sSort; $sSql = $sSelect; if ($sDbWhere <> "") $sSql .= " WHERE " . $sDbWhere; if ($sGroupBy <> "") $sSql .= " GROUP BY " . $sGroupBy; if ($sHaving <> "") $sSql .= " HAVING " . $sHaving; if ($sDbOrderBy <> "") $sSql .= " ORDER BY " . $sDbOrderBy; return $sSql; } $sSql = (!empty($_GET['type'])) ? $vehicles->SQL() . " WHERE type = '" . mysql_real_escape_string($_GET['type']) . "'" : $vehicles->SQL(); OR // Call Row Selecting event $vehicles->Row_Selecting($sFilter); // Load sql based on filter $vehicles->CurrentFilter = $sFilter; $sSql = $vehicles->SQL(); if ($rs = $conn->Execute($sSql)) { if ($rs->EOF) { $LoadRow = FALSE; } else { $LoadRow = TRUE; $rs->MoveFirst(); $this->LoadRowValues($rs); // Load row values // Call Row Selected event $vehicles->Row_Selected($rs); } $rs->Close(); } else { $LoadRow = FALSE; } return $LoadRow; } Are my nearest two guesses?

I figured there might be a keyword like BUILD that I could search for? // Table SQL function SQL() { $sFilter = $this->CurrentFilter; $sSort = $this->getSessionOrderBy(); return ew_BuildSelectSql($this->SqlSelect(), $this->SqlWhere(), $this->SqlGroupBy(), $this->SqlHaving(), $this->SqlOrderBy(), $sFilter, $sSort); } What are the chances that that is it?

Thanks both of you for the enlightening.. Any ideas as to where I would find out where the SQL is constructed?

Ok.. but isn't: (code from above) $sUrlParm = $this->UrlParm("type=" . mysql_real_escape_string($_GET['type']) . "&order=" . urlencode($fld->FldName) . "&ordertype=" . $fld->ReverseSort()); Telling it to go in the right direction? It WAS working and I don't know what changed.

The problem is that it's not. Even if I set my Cars button to: http://craighooghiem.com/goodwills/final/inventory.php?type=car&order=make&ordertype=ASC I get errors. The output still shows the Order before the WHERE regardless of the fact that I haven't told it to do that anywhere that I can find.

Failed to execute SQL. Error: 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 'WHERE type = 'car'' at line 1 If I echo the variable after the execution I get: SELECT * FROM `vehicles` ORDER BY `model` ASC WHERE type = 'car' I know that Where has to go first, but the place where it was defined (this was working 30 minutes ago) should be: // Sort Url function SortUrl(&$fld) { if ($this->CurrentAction <> "" || $this->Export <> "" || ($fld->FldType == 205)) { // Unsortable data type return ""; } else { $sUrlParm = $this->UrlParm("type=" . mysql_real_escape_string($_GET['type']) . "&order=" . urlencode($fld->FldName) . "&ordertype=" . $fld->ReverseSort()); return ew_CurrentPage() . "?" . $sUrlParm; } } Which defines the type before the other two..

I know I know.. I'm back again with this problem. Here's the issue: a) I have the code: // Table SQL function SQL() { $sFilter = $this->CurrentFilter; $sSort = $this->getSessionOrderBy(); return ew_BuildSelectSql($this->SqlSelect(), $this->SqlWhere(), $this->SqlGroupBy(), $this->SqlHaving(), $this->SqlOrderBy(), $sFilter, $sSort); } Sorting tables works great, but as soon as I navigate to http://www.craighooghiem.com/goodwills/final/inventory.php?type=trucks I get errors. Comment out: $sSort = $this->getSessionOrderBy(); Then sort no longer works, but i can display the URLs fine. Anyone have any ideas what the problem is? More details to follow if you know what you need to know. Thanks!