Dynamic queries (via user action) to display on page

[BC]

Hi all,

Newbie question, and I need some help pretty urgently so any help would be greatly appreciated.

I'm currently working on a site with PHP/SQL Server (don't ask). I have the correct ODBC query, so now my question is this:

The site is currently all plain HTML with A-Z listings of mobile ringtones (so each letter of the alphabet has its own HTML page). I'm currently converting the pages into a PHP-driven page, with results pulled directly from SQL Server.

So what I want to know is, when someone clicks on a letter to pull the correct results from the table, what is the correct PHP code?

(one other caveat: we'd like it to be done via the POST method so that the query string etc. are all hidden)

Many thanks in advance for any assistance offered.

[CeleronXL]

Well, you might have a ringtones.php file for this page. And then you could link all the pages like ringtones.php?p=A, ringtones.php?p=B, etc for each letter.

Then, somewhere in ringtones.php you could have the query "SELECT * FROM ringtones WHERE letter='$p'";

Obviously the syntax won't be exactly like that, but you get the idea of how to call it.

[BC]

So the variable to call would be $p and then query it in the URL via 'p=letter'?

[redemption]

What is your table schema like?

CeleronXL has made the perfect suggestion of using the query string, so you can get at the variable using $_GET['p'] (for his example above).

If your table has a field for the name of the ringtone (say 'name'), you can get at it with the following query.

$sql = 'SELECT field1, field2
FROM ringtones
WHERE name LIKE \'' . $_GET['p'] . '%\''; 

The actual query after PHP has filled in the variable value is something like this:

SELECT field1, field2
FROM ringtones
WHERE name LIKE 'B%';

[BC]

Thanks Joel. That puts me on the right track... Now to debug some SQL errors!

The following is my PHP/SQL query....

<?php

$query = "SELECT * from RtList where [Artist] LIKE \'' . $_GET['p'] . '%\'';"; 

// Original test query 'C%' or [Artist] LIKE 'c%' ORDER BY Artist
          
    $conn = odbc_connect("RingToneDB", "", "" ); 

    if (!$conn) {
        print "Connection failed\n</html>";
        exit;   
    }
    if ($p = odbc_Exec($conn, $query)) 
    {
        print "Query returned : " . odbc_num_rows($p) . " rows";
        odbc_result_all($p, "border = 0");
    }

    odbc_close($conn); 

?>

Now I'm getting the following errors -

Notice: Undefined index: 'p' in C:\Program Files\Apache Group\Apache2\htdocs\test.php on line 5

Warning: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '\'., SQL state 37000 in SQLExecDirect in C:\Program Files\Apache Group\Apache2\htdocs\test.php on line 15

(Line 1 being the $query line, and line 15 being if ($result = odbc_Exec))

Any SQL gurus here?

[BC]

A slight change. After further reading, I've changed the code to

<?php 
    
error_reporting(E_ALL & ~E_NOTICE);

    function get_Results() {
    
    if(isset($_GET['rt'])) {
    $rt = $_GET['rt'];
    }

    $sql = "SELECT * from RtList where [Artist] LIKE \'' . $rt . '%\'' ORDER BY Artist"; 
    
    // $sql = "SELECT * from RtList where [Artist] LIKE 'C%' or [Artist] LIKE 'c%' ORDER BY Artist"; 
          
    $conn = odbc_connect("RingToneDB", "", "" ); 

    if (!$conn) {
        print "Connection failed\n</html>";
        exit;   
    }
    if ($result = odbc_Exec($conn, $sql))  // Line 24
    {
        print "Query returned : " . odbc_num_rows($result) . " rows";
        odbc_result_all($result, "border = 0");
    }

    odbc_close($conn); 
    }
    
?>
<?php get_Results(); ?>
<a href="test_3.php?rt=$rt">Test T RingTones</a>

However, line 24 is still killing the script. Still getting the following error:

Warning: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '\'., SQL state 37000 in SQLExecDirect in C:\Program Files\Apache Group\Apache2\htdocs\test_3.php on line 24

Any idea??

(getting here)

TIA

[Powerlord]

You're mixing single and double quotes in the following line:

$sql = "SELECT * from RtList where [Artist] LIKE \'' . $rt . '%\'' ORDER BY Artist"; 

It should read (recommended style):

$sql = 'SELECT * from RtList where [Artist] LIKE \'' . $rt . '%\' ORDER BY Artist'; 

or (alternative style):

$sql = "SELECT * from RtList where [Artist] LIKE '{$rt}%' ORDER BY Artist"; 

Note the color change of the $rt in the recommended style php block. It shows up as blue to show that it is a variable and not part of the string.

Also, is Artist supposed to be in brackets in those selects? I don't know SQL Server or ODBC, but standard SQL doesn't have brackets around a column name.

[BC]

Many thanks PowerLord. Your solution should work, but I managed to get help from SP's PHP guru (Jules S.) who told me to try:

$sql = "SELECT * from RtList where [Artist] LIKE '".$rt."%' ORDER BY Artist"; 

And this works perfectly.

In answer to the square brackets around the column name, yeah, normal SQL usually doesn't need them as far as I'm aware... But it's more for clarity than anything.

Thanks to all for their assistance.