SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast dave_merwin's Avatar
    Join Date
    Apr 2003
    Location
    Eugene, OR
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple MySQL Querries resulting in 2 duplicate

    I am trying to write a quote system. It is not a true shopping cart, however it does have some of the same functionality. It will interact with a session class soon, but I just need it to work first. The following is creating 2 records everytime it runs. It is driving me crazy trying ot figure out why. If you have a better way... let me know. I realize that there are PEAR elements that could easily do this, but I want to iron this out so I can learn it.

    Am I totally off? Please advise.

    Anyway, code below.

    PHP Code:
    <?
    //What am I looking for?
    $pid $_GET['pid'];
    $oid $_GET['oid'];
    $del $_GET['del'];

    //Has an item been removed?
    if (isset($del)) {
        
        
    //remove the item from the database
        
    $result $db->query ('DELETE FROM orders WHERE oid = "'.$oid.'" AND pid = "'.$pid.'" LIMIT 1');
        
    //Is there an oid?
    } elseif (isset($oid)) {

        
    //add item to cart
        
    $result $db->query ('UPDATE orders SET pid="'.$pid.'", order_date = "'.$order_date.'", quantity = "'.$qnty.'" WHERE oid = "'.$oid.'" ');

    } else {

        
    //Set the oid
        
    $oid $_SERVER['REMOTE_ADDR'].mktime();
        
    $order_date date('m-d-Y');
        
    //create the first item in an order and asign the oid
        
    $result $db->query('INSERT INTO orders (pid, order_date, oid) VALUES ("'$pid .'", "'.$order_date .'", "'$oid.'")');

    }

    //Get all the records and assemble for display
    $result $db->query ('SELECT products.name, products.description_short, products.map, orders.pid, orders.oid, orders.quantity FROM products, orders WHERE orders.oid="'.$oid.'" AND products.id = "'.$pid.'" ');
    while(
    $row=mysql_fetch_array($result)){
        
    $cart_item .= '
            <tr class="item-row">
                <td><a href="'
    .$_SERVER['PHP_SELF'].'?del=1&pid='.$pid.'"><img src="media/control_imgs/delete.gif" alt="del" /></a></td>
                <td><input type="text" id="qnty" name="qnty" class="quantity" value="'
    .$qnty $row['quantity'].'"></td>
                <td>'
    .$item $row['name'].'</td>
                <td>'
    .$description $row['description_short'].'</td>
                <td><strong>$'
    .$map $row['map'].'</strong></td>
                <td></td>
            </tr>'
    ;
        }


    //Assemble Everything
    $display '
    <table>
    <thead>
    <tr>
        <td></td>
        <td>qnty</td>
        <td>item</td>
        <td>description</td>
        <td>MAP</td>
        <td>item total</td>
    </tr>
    </thead>
    <tfoot>
        <tr>
            <td colspan="3"></td>
            <td class="text-right">Estimated Total</td>
            <td colspan="2" class="alert">CALL FOR BEST PRICE</td>
        </tr>
    </tfoot>
    <tbody>'
    .$cart_item.'</tbody>
    </table>
    '
    ;

    ?>
    Dave Merwin

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    when you say "creating 2 records" does this mean are you getting multiple rows inserted into the database?

    or are you simply displaying more rows than you expect?

    your SELECT query is missing a join condition

    try running these two queries separately, not through php, in a front end like phpmyadmin so that you can see the actual data --

    select id, name, description_short, map
    from products where products.id = pid

    select pid, oid, quantity
    from orders where orders.oid = oid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast dave_merwin's Avatar
    Join Date
    Apr 2003
    Location
    Eugene, OR
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying AND Inserting

    I am displaying AND inserting 2 records at the same time.

    I will try the SQL you suggested.

    The products select worked great.

    However, the orders select was terrible. The following is the error I received. The OID uses the machine ip so... I have to strip out those "." when I create the id. I won't waste your time with that. I can look it up in the morning.

    Here is my error.

    ERROR: Invalid Identifer @ 77
    STR: 127.0.
    SQL: SELECT orders.pid, orders.oid, orders.quantity FROM orders WHERE orders.oid=127.0.0.11107302996

    SQL-query :

    SELECT orders.pid, orders.oid, orders.quantity FROM orders WHERE orders.oid=127.0.0.11107302996

    MySQL said:
    #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 '.0.11107302996' at line 1
    Dave Merwin

  4. #4
    SitePoint Enthusiast dave_merwin's Avatar
    Join Date
    Apr 2003
    Location
    Eugene, OR
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, figured out the problem.
    The problem was the function that was being used. I ran an error check, but my logic was off. The error check ran any query twice.

    Interesting.

    See below. Thanks for all you help!

    PHP Code:
    function query($sql){
      if(!
    $this->db_connection$this->connect_to_db ();
     
    //This ran the query first time if (!mysql_query($sql, $this->db_connection)) echo ('<p>'.mysql_error().'</p>');
      
    return mysql_query($sql$this->db_connection); //This ran it again

    Dave Merwin


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
  •