SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Irritability Defined
    Join Date
    Jul 2000
    Location
    80,000 feet below the surface
    Posts
    1,442
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Dynamic queries (via user action) to display on page

    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.
    My 2 Cents (or is that 2.2 Cents including GST?)

  2. #2
    SitePoint Addict CeleronXL's Avatar
    Join Date
    Dec 2002
    Location
    United States of America
    Posts
    349
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    Irritability Defined
    Join Date
    Jul 2000
    Location
    80,000 feet below the surface
    Posts
    1,442
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So the variable to call would be $p and then query it in the URL via 'p=letter'?
    My 2 Cents (or is that 2.2 Cents including GST?)

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    $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:
    Code:
    SELECT field1, field2
    FROM ringtones
    WHERE name LIKE 'B%';

  5. #5
    Irritability Defined
    Join Date
    Jul 2000
    Location
    80,000 feet below the surface
    Posts
    1,442
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Joel. That puts me on the right track... Now to debug some SQL errors!

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

    PHP Code:

    <?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?
    Last edited by BC; May 25, 2003 at 19:15.
    My 2 Cents (or is that 2.2 Cents including GST?)

  6. #6
    Irritability Defined
    Join Date
    Jul 2000
    Location
    80,000 feet below the surface
    Posts
    1,442
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A slight change. After further reading, I've changed the code to

    PHP Code:
    <?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
    My 2 Cents (or is that 2.2 Cents including GST?)

  7. #7
    SitePoint Enthusiast Powerlord's Avatar
    Join Date
    May 2003
    Location
    Mason, MI, USA
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're mixing single and double quotes in the following line:
    PHP Code:
    $sql "SELECT * from RtList where [Artist] LIKE \'' . $rt . '%\'' ORDER BY Artist"
    It should read (recommended style):
    PHP Code:
    $sql 'SELECT * from RtList where [Artist] LIKE \'' $rt '%\' ORDER BY Artist'
    or (alternative style):
    PHP Code:
    $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.
    Ross Bemrose,
    Independant MySQL/Perl/PHP Developer

  8. #8
    Irritability Defined
    Join Date
    Jul 2000
    Location
    80,000 feet below the surface
    Posts
    1,442
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $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.
    My 2 Cents (or is that 2.2 Cents including GST?)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •