www.webdeveloper.com
Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 50

Thread: Search Function with multiple variables in SQL

  1. #31
    Join Date
    Apr 2009
    Posts
    107
    Ok made the fix, here is the new code:
    PHP Code:
    <html>
    <body>

    <?php 
    include "db.php";
    include 
    "error.php";

    $tbl_name="product";

    // Connect to server and select database.
    mysql_connect($hostname$username$password)or die("cannot connect"); 
    mysql_select_db($database)or die("cannot select DB"); 

    $total_result mysql_query"SELECT* FROM $tbl_name)  
        or die(
    "SELECT Error: ".mysql_error());  

    $sql "SELECT COUNT(*) FROM $tbl_name";
    $sql mysql_query($sql);
    $num_rows mysql_fetch_row($sql);
    $num_rows $num_rows[0]; 

    // Retrieve data from database 
    $sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
    $result=mysql_query($sql); 

    $sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
    echo 
    "SQL: ",$sql,"<br />\n"

    // Start looping rows in mysql database. 
    while ($rows=mysql_fetch_assoc($result)) { 
    ?> 

    <p><?php echo $rows['product'];  ?></p>

    <?php
    // close while loop 
    }

    // close connection 
    mysql_close();
    ?>

    </body>
    </html>
    and here is what displays now:
    SQL: SELECT * FROM product WHERE technology = '1' AND range = '2' AND output = '1' AND configuration='2'

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Program Files\Apache Group\Apache2\htdocs\selection\product_view.php on line 30

  2. #32
    Join Date
    Sep 2008
    Posts
    408
    Do you have access to phpMyAdmin or just MySQL?

    If so, plump in
    Code:
    SELECT * FROM product WHERE technology = '1' AND range = '2' AND output = '1' AND configuration='2'
    And then see what that gets or if there is an error.
    Mullanaphy!
    http://www.mullanaphy.com/

    Unless code is provided or an exact example is requested I think I'm going to start using psuedo code from now on...

    Also, I freelance as well. Inquire within!

  3. #33
    Join Date
    Nov 2008
    Posts
    2,477
    We need to see what error the mysql_query function is producing. Change this:

    PHP Code:
    // Retrieve data from database
    $sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
    $result=mysql_query($sql); 
    To this:

    PHP Code:
    // Retrieve data from database
    $sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
    $result=mysql_query($sql) or die(mysql_error()); 

  4. #34
    Join Date
    Apr 2009
    Posts
    107
    Shorts, using phpmyadmin I do get an error, here it is:
    #1064 - 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 'range = '2' AND output = '1' AND configuration LIMIT 0, 30' at line 1
    Not sure what it means

  5. #35
    Join Date
    Apr 2009
    Posts
    107
    Mindzai, here is the error that I get when I change the PHP code to what you suggested:
    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 'range = '2' AND output = '1' AND configuration='2'' at line 1

  6. #36
    Join Date
    Jan 2009
    Posts
    3,346
    Try changing this:
    Code:
    $result=mysql_query($sql);
    To:
    Code:
    $result=mysql_query($sql) or die(mysql_error());
    See if we get a new error now.


    *edit* I didn't see everyone suggest that above before I posted...oops.

  7. #37
    Join Date
    Sep 2008
    Posts
    408
    Thought so, change range into `range` and see what happens.

    Edit: For reference here's why:
    http://dev.mysql.com/doc/refman/5.1/...ved-words.html

    And hehe criterion9, the more the merrier.

    P.S. this helped demonstrate debugging methods and brought to light SQL Injection. Important stuff in developing. :3
    Last edited by Shorts; 06-16-2009 at 05:07 PM.
    Mullanaphy!
    http://www.mullanaphy.com/

    Unless code is provided or an exact example is requested I think I'm going to start using psuedo code from now on...

    Also, I freelance as well. Inquire within!

  8. #38
    Join Date
    Apr 2009
    Posts
    107
    Ok shorts here is the new code
    PHP Code:
    <html>
    <body>

    <?php 
    include "db.php";
    include 
    "error.php";

    $tbl_name="product";

    // Connect to server and select database.
    mysql_connect($hostname$username$password)or die("cannot connect"); 
    mysql_select_db($database)or die("cannot select DB"); 

    $total_result mysql_query"SELECT* FROM $tbl_name)  
        or die(
    "SELECT Error: ".mysql_error());  

    $sql "SELECT COUNT(*) FROM $tbl_name";
    $sql mysql_query($sql);
    $num_rows mysql_fetch_row($sql);
    $num_rows $num_rows[0]; 

    // Retrieve data from database 
    // Retrieve data from database
    $sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND 'range' = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
    $result=mysql_query($sql) or die(mysql_error()); 

    $sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
    echo 
    "SQL: ",$sql,"<br />\n"

    // Start looping rows in mysql database. 
    while ($rows=mysql_fetch_assoc($result)) { 
    ?> 

    <p><?php echo $rows['product'];  ?></p>

    <?php
    // close while loop 
    }

    // close connection 
    mysql_close();
    ?>

    </body>
    </html>
    Here is what shows up on the screen, still no results
    SQL: SELECT * FROM product WHERE technology = '1' AND range = '2' AND output = '1' AND configuration='1'

  9. #39
    Join Date
    Jan 2009
    Posts
    3,346
    He used the tick mark not a single quote. The tick mark is usually next to the number 1 on US keyboards.

  10. #40
    Join Date
    Apr 2009
    Posts
    107
    Ok made them tick marks, here is the corrected code:
    PHP Code:
    <html>
    <body>

    <?php 
    include "db.php";
    include 
    "error.php";

    $tbl_name="product";

    // Connect to server and select database.
    mysql_connect($hostname$username$password)or die("cannot connect"); 
    mysql_select_db($database)or die("cannot select DB"); 

    $total_result mysql_query"SELECT* FROM $tbl_name)  
        or die(
    "SELECT Error: ".mysql_error());  

    $sql "SELECT COUNT(*) FROM $tbl_name";
    $sql mysql_query($sql);
    $num_rows mysql_fetch_row($sql);
    $num_rows $num_rows[0]; 

    // Retrieve data from database
    $sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND `range` = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
    $result=mysql_query($sql) or die(mysql_error()); 

    $sql="SELECT * FROM $tbl_name WHERE technology = '".$_GET['technology']."' AND range = '".$_GET['range']."' AND output = '".$_GET['output']."' AND configuration='".$_GET['configuration']."'";
    echo 
    "SQL: ",$sql,"<br />\n"

    // Start looping rows in mysql database. 
    while ($rows=mysql_fetch_assoc($result)) { 
    ?> 

    <p><?php echo $rows['product'];  ?></p>

    <?php
    // close while loop 
    }

    // close connection 
    mysql_close();
    ?>

    </body>
    </html>

    SQL: SELECT * FROM product WHERE technology = '1' AND range = '2' AND output = '1' AND configuration='2'
    echosonicII
    So I got the right result, it worked!! But why did making the tick mark around Range make it work. I would like to learn from my mistake if someone can explain. Also, is this even good code, or would someone know a better way of writing it?

  11. #41
    Join Date
    Apr 2009
    Posts
    107
    If I wanted to change the database from to work with characters other than integers, would the code stay the same or would I change something / make it easier?

  12. #42
    Join Date
    Nov 2008
    Posts
    2,477
    Range is a mysql reserved word, so using ticks tells mysql you are not referring to its own 'range'.

  13. #43
    Join Date
    Sep 2008
    Posts
    408
    Good to hear,

    This page will explain more: http://dev.mysql.com/doc/refman/5.1/...ved-words.html

    Pretty much, RANGE is a reserved word. Since there is a MySQL function RANGE. It would be the same if you named your column SELECT instead of RANGE, you'd then have to use back ticks to make it work.

    That way MySQL knows you want a variable named RANGE instead of doing the command RANGE. With ticks that is.

    My suggestions (with new code only for the middle):

    PHP Code:
    $sql="SELECT * FROM $tbl_name WHERE technology = '".intval($_GET['technology'])."' AND `range` = '".intval($_GET['range'])."' AND output = '".intval($_GET['output'])."' AND configuration='".intval($_GET['configuration'])."'";
    echo 
    "SQL: ",$sql,"<br />\n";

    // Start looping rows in mysql database.
    while ($rows=mysql_fetch_assoc($result)) {
      echo 
    '<p>',$rows['product'],'</p>';

    First, the intval()'s make sure that the data going into the SQL are numbers. This will stop people using strings like:

    Code:
    http://yoursite.com/yourpage.php?technology=';DROP TABLE product;
    Or other malicious SQL Injections.

    Second, just personal preference and more efficient. It is more efficient to not keep dropping in and out of parsing mode, so echoing instead of ?><?. Also, personally use commas for echos instead of periods as PHP will just keep pushing the data to screen instead of joining the string and then pushing it.

    All and all, there isn't anything wrong (other then the SQL Injection part). As for the efficiencies, for the most part that is all personal preference.
    Mullanaphy!
    http://www.mullanaphy.com/

    Unless code is provided or an exact example is requested I think I'm going to start using psuedo code from now on...

    Also, I freelance as well. Inquire within!

  14. #44
    Join Date
    Apr 2009
    Posts
    107
    Makes sense, was just able to read the article "Shorts" posted.

  15. #45
    Join Date
    Apr 2009
    Posts
    107
    Thanks shorts, that all makes sense. Learned a lot of new stuff today. Thanks guys.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles