Table Sort vs. ?type=car Conflict

[gamerzfuse]

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!

 

[Maq]

What do your errors say?

[gamerzfuse]

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..

 

[Ken2k7]

Your WHERE clause should go before your ORDER BY clause.

[gamerzfuse]

I know that Where has to go first

 

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.

 

[Ken2k7]

The problem doesn't lie in the query string. It lies in how your system builds the SQL string.

[gamerzfuse]

The problem doesn't lie in the query string. It lies in how your system builds the SQL string.

 

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.

[gevans]

That's building your url. You need to find out where your code is building your sql

[Ken2k7]

That's just building the query string, not the SQL. Just so you know, it doesn't matter what order you put them in the query string. If the SQL is constructed correctly, it doesn't matter where you put type in the query string.

[gamerzfuse]

That's just building the query string, not the SQL. Just so you know, it doesn't matter what order you put them in the query string. If the SQL is constructed correctly, it doesn't matter where you put type in the query string.

 

Thanks both of you for the enlightening..

Any ideas as to where I would find out where the SQL is constructed?

[Ken2k7]

*Ken2k7 thinks...

 

No.... How do you expect us to know that?

[gamerzfuse]

*Ken2k7 thinks...

 

No.... How do you expect us to know that?

 

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?

[gevans]

The error that prints on the page should tell you the filename and page number.

[gevans]

you need to find the function ew_BuildSelectSql()

[Ken2k7]

Can you post the code or line where you actually called those SQL methods?

[gamerzfuse]

you need to find the function ew_BuildSelectSql()

 

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;
}

 

Can you post the code or line where you actually called those SQL methods?

 

$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?

[Ken2k7]

Change:

$sSql = (!empty($_GET['type'])) ? $vehicles->SQL() . " WHERE type = '" . mysql_real_escape_string($_GET['type']) . "'" : $vehicles->SQL();

 

To

$sSql = (!empty($_GET['type'])) ? $vehicles->SqlSelect() . " WHERE type = '" . mysql_real_escape_string($_GET['type']) . "'" : $vehicles->SQL();

[gamerzfuse]

Change:

$sSql = (!empty($_GET['type'])) ? $vehicles->SQL() . " WHERE type = '" . mysql_real_escape_string($_GET['type']) . "'" : $vehicles->SQL();

 

To

$sSql = (!empty($_GET['type'])) ? $vehicles->SqlSelect() . " WHERE type = '" . mysql_real_escape_string($_GET['type']) . "'" : $vehicles->SQL();

 

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 "";
}

[Ken2k7]

Yeah, well that's not my problem. I'm not going through every single possible page on your site to fix the sort problem. I already gave you the solution. You can fix the rest or pay someone to fix the rest.

[gamerzfuse]

Yeah, well that's not my problem. I'm not going through every single possible page on your site to fix the sort problem. I already gave you the solution. You can fix the rest or pay someone to fix the rest.

 

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.

[Ken2k7]

Can you output:

var_dump($_GET['type']);

[gamerzfuse]

Can you output:

var_dump($_GET['type']);

 

string(3) "car"

[Ken2k7]

Well it should be correct then. Can you be more specific in what you mean by it doesn't sort? And no, I'm not searching through your site, sorry.

[gamerzfuse]

Well it should be correct then. Can you be more specific in what you mean by it doesn't sort? And no, I'm not searching through your site, sorry.

 

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.

[Ken2k7]

You're missing the ORDER BY clause in your SQL.