altering query based on URL parameters

[Shamrox]

I'd like to dynamically display data from my table and have links across the top that the user could easily click to alter the query. For instance, the list would be all of the sales reps names. If you click one name, the query would be filtered to show just that reps data. Ok, that's the easy part that I have working, but I don't know what to put in the URL string for ALL records to show. here's what I do now.

domainname.com/page.php?repid=1

the repid would change based on the link clicked and filters the data.

What would be used for ALL records to show? A wildcard of some sort?

Thanks.

[Psycho]

Personally I would use nothing at all.

[code]<?php

if (is_numeric($_GET['repid']) && $_GET['repid'] >= 0) {
    $whereClause = "WHERE repid =  $_GET['repid']";
} else {
    $whereClause = "";
}

$sql = "SELECT * FROM table " . $whereClause;

?>[/code]

[Shamrox]

I completely understand what the above code is achieving, but I'm having the worst time integrating it into my present code. I can't seem to get the syntax correct. How would I put the above into this and make it work?

[code]
$colname_rs_test = "0";
if (isset($_GET['rep'])) {
  $colname_rs_test = (get_magic_quotes_gpc()) ? $_GET['rep'] : addslashes($_GET['rep']);
}
mysql_select_db($database_spectrum, $spectrum);
$query_rs_test = sprintf("SELECT `spec_course`.`ctitle`, `spec_client`.`name`, `spec_contacts`.`lastname`, DATE_FORMAT(`spec_registrar`.`orderdate`, '%%c/%%e/%%Y') AS `orderdate1`, DATE_FORMAT(`spec_registrar`.`startdate`, '%%c/%%e/%%Y') AS `startdate1`,`spec_registrar`.* FROM `spec_registrar`  INNER JOIN `spec_students` ON `spec_registrar`.`studentid` = `spec_students`.`studentid` INNER JOIN `spec_client` ON `spec_client`.`clientid` = `spec_students`.`clientid` INNER JOIN `spec_contacts` ON `spec_contacts`.`contactid` = `spec_students`.`contactid` INNER JOIN `spec_course` ON `spec_course`.`course_num` = `spec_registrar`.`coursecode` WHERE `spec_registrar`.`invoiced_status` <> 'on' AND `spec_registrar`.`stbrepid`=%s ORDER BY `spec_registrar`.`registrarid` DESC ", GetSQLValueString($colname_rs_test, "int"));
$query_limit_rs_test = sprintf("%s LIMIT %d, %d", $query_rs_test, $startRow_rs_test, $maxRows_rs_test);
$rs_test = mysql_query($query_limit_rs_test, $spectrum) or die(mysql_error());
$row_rs_test = mysql_fetch_assoc($rs_test);
[/code]

Your $whereClause would replace the second part of my WHERE. Just can't seem to integrate.

Thanks

[Psycho]

Personally I don't use sprintf for queries as it makes my code unreadable, but the below code should work.

[code]<?php
if (isset($_GET['rep']))
  $repid = (get_magic_quotes_gpc()) ? $_GET['rep'] : addslashes($_GET['rep']);
}

$colname_rs_test = ($repid) ? " AND `spec_registrar`.`stbrepid` = $repid" : "";

mysql_select_db($database_spectrum, $spectrum);
$query_rs_test = sprintf("SELECT `spec_course`.`ctitle`, `spec_client`.`name`, `spec_contacts`.`lastname`,
                              DATE_FORMAT(`spec_registrar`.`orderdate`,'%%c/%%e/%%Y') AS `orderdate1`,
                              DATE_FORMAT(`spec_registrar`.`startdate`, '%%c/%%e/%%Y') AS `startdate1`,`spec_registrar`.*
                          FROM `spec_registrar`
                          INNER JOIN `spec_students` ON `spec_registrar`.`studentid` = `spec_students`.`studentid`
                          INNER JOIN `spec_client` ON `spec_client`.`clientid` = `spec_students`.`clientid`
                          INNER JOIN `spec_contacts` ON `spec_contacts`.`contactid` = `spec_students`.`contactid`
                          INNER JOIN `spec_course` ON `spec_course`.`course_num` = `spec_registrar`.`coursecode`
                          WHERE `spec_registrar`.`invoiced_status` <> 'on' %s
                          ORDER BY `spec_registrar`.`registrarid` DESC
                          LIMIT %d, %d", GetSQLValueString($colname_rs_test, "int"), $startRow_rs_test, $maxRows_rs_test);

$rs_test = mysql_query($query_limit_rs_test, $spectrum) or die(mysql_error());
$row_rs_test = mysql_fetch_assoc($rs_test);
?>[/code]

[Shamrox]

I don't like the sprintf much either, but I generate a lot of code quickly in Dreamweaver and it forces it upon you. When i try to hand code things, it gets a bit garbled in there.

Anyway, i'll give your code a whirl and see how it pans out. Thanks for your time.

[Shamrox]

Hmmm, it now returns "Query is empty"