Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PHP - working with multiple sql where clauses

    I am writing a small php application that returns results based on information a user can enter into multiple text fields.

    here is what I have:

    search.php
    -------------
    Order Info ID:
    <INPUT type="text" maxLength=100 size=17 name=orderId>
    TELEPHONE:
    <INPUT type="text" maxLength=20 size=14 name=phone>
    PON:
    <INPUT type="text" maxLength=100 size=27 name=ponValue>


    results.php
    ----------------
    <?
    $pon = $_POST['ponValue'];
    $telephone = $_POST['phone'];
    $order = $_POST['orderID'];

    $select = "select * from orderinfo and day <>'' " ;
    $whereClause = "";
    $res = $db_connection->Execute($select);


    ?>

    <?
    //this is what I don't know how to do.
    //I need to add the where clause to the sql statement if the user
    //..has entered a value into any of the text fields

    if($pon !="")
    {
    whereClause = " and pon = ".'$pon';
    echo $res->Fields['$pon']->Value;
    }
    ?>

    How can I add this where clause to my sql statment based on what value the user has entered?

    please help.

    thanks,
    Mark

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    PHP Code:
    $select "select * from orderinfo and day <>'' " ;
    $whereClause "";
    if(isset(
    $pon))
      
    $where.='pon='.mysql_real_escape_string($pon).' AND';
    if(isset(
    $telephone))
      
    $where.='telephone='.mysql_real_escape_string($telephone).' AND';
    if(isset(
    $order))
      
    $where.='order='.mysql_real_escape_string($order).' AND';
    if(
    $where!='')
      
    $where=' WHERE '.substr($where,0,-4);
    $select.=$where;
    $res $db_connection->Execute($select); 

  • #3
    New to the CF scene
    Join Date
    May 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hmmm,

    Quote Originally Posted by GJay
    PHP Code:
    $select "select * from orderinfo and day <>'' " ;
    $whereClause "";
    if(isset(
    $pon))
      
    $where.='pon='.mysql_real_escape_string($pon).' AND';
    if(isset(
    $telephone))
      
    $where.='telephone='.mysql_real_escape_string($telephone).' AND';
    if(isset(
    $order))
      
    $where.='order='.mysql_real_escape_string($order).' AND';
    if(
    $where!='')
      
    $where=' WHERE '.substr($where,0,-4);
    $select.=$where;
    $res $db_connection->Execute($select); 

    Don't want to sound pushy, but there are a couple of other ways to do this.

    PHP Code:
    $select "select * from orderinfo and day <>'' " ;
    $whereClause "";
    if(!empty(
    $pon))
      
    $where.='pon='.mysql_real_escape_string($pon).' AND';
    if(!empty(
    $telephone))
      
    $where.='telephone='.mysql_real_escape_string($telephone).' AND';
    if(!empty(
    $order))
      
    $where.='order='.mysql_real_escape_string($order).' AND';
    if(
    $where!='')
      
    $where=' WHERE '.substr($where,0,-4);
    $select.=$where;
    $res $db_connection->Execute($select); 

    by using the empty function you stop someone from entering blank characters in a form which would not get caught by isset, and would trigger an error.
    Although, If your a fan of arrays... You might try this.


    PHP Code:

    $select 
    "select * from orderinfo and day <>'' " ;

    if(
    count($_GET) > 3){

    exit; 
    // Someone is trying to inject get variables!

    }
    elseif(
    count($_GET) > 1){

    foreach(
    $_GET as $x => $y){

    $arr_where[] = "(`" $x "` LIKE '" $y "')";

    }

    $str_where implode(" AND ",$arr_where);

    $select .= " WHERE " $str_where;


    This would allow for scalability. If suddenly you need to ask for more information, It is all still optional just change the 3 in the first if statement to the total number of items your form should return.

  • #4
    New Coder
    Join Date
    Jul 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    phpfr33k,

    I am giving the user the option of either entering 1, multiple or, no values. Will the empty function still work?

  • #5
    New Coder
    Join Date
    Jul 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I neglected to mention something, I have this in a while loop so basically it looks like this....

    search.php
    -------------
    Order Info ID:
    <INPUT type="text" maxLength=100 size=17 name=orderId>
    TELEPHONE:
    <INPUT type="text" maxLength=20 size=14 name=phone>
    PON:
    <INPUT type="text" maxLength=100 size=27 name=ponValue>


    results.php
    ----------------
    <?
    $pon = $_POST['ponValue'];
    $telephone = $_POST['phone'];
    $order = $_POST['orderID'];

    $select = "select * from orderinfo and day <>'' " ;
    $whereClause = "";
    $res = $db_connection->Execute($select);


    ?>

    <?
    //this is what I don't know how to do.
    //I need to add the where clause to the sql statement if the user
    //..has entered a value into any of the text fields

    while(!res->EOF)
    {

    if($pon !="")
    {
    whereClause = " and pon = ".'$pon';
    echo $res->Fields['$pon']->Value;
    }
    }
    ?>


    So where would I add the code if it's in a while loop?


  •  

    Posting Permissions

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