Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,609

  • Joined

  • Last visited

  • Days Won

    832

 Content Type 

Everything posted by Barand

  1. Barand
    this code will demonstrate how to use ajax to change the three prices when a different product is selected The form <?php $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); function productOptions($db) { $sql = "SELECT id, name FROM product ORDER BY name"; $res = $db->query($sql); $opts = "<option value=''>- choose -</option>\n"; while (list($id,$name) = $res->fetch_row()) { $opts .= "<option value='$id'>$name</option>\n"; } return $opts; } ?> <html> <head> <title>Sample</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type='text/javascript'> $().ready(function() { $("#product").change(function() { var pid = $(this).val(); $.get( "get_prod_prices.php" , {"pid":pid} , function(data) { $.each(data, function(k,v) { $("#price"+k).val(v); if (v) $("#label"+k).html("€"+v); else $("#label"+k).html(""); }) }, "JSON" ) }) }) </script> </head> <body> <form> <select name="product" id="product" > <?=productOptions($db)?> </select> <input type="radio" name="price" id="price1" value="0"><label for="price1" id="label1"> </label> <input type="radio" name="price" id="price2" value="0"><label for="price2" id="label2"> </label> <input type="radio" name="price" id="price3" value="0"><label for="price2" id="label3"> </label> </form> </body> </html> the script call with AJAX request (get_prod_prices.php) <?php $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); if (!isset($_GET['pid'])) { $prices = [1=>'','','']; exit(json_encode($prices)); } $pid = intval($_GET['pid']); $sql = "SELECT price1, price2, price3 FROM product WHERE id = $pid"; $res = $db->query($sql); list($p1, $p2, $p3) = $res->fetch_row(); $prices = [1=>$p1, $p2, $p3]; echo json_encode($prices); ?>
  2. Barand
    FYI I ran a similar query on two of my table using a JOIN ON x LIKE ('%y%') as you were trying Table 1: 9,500 rows Table 2: 600,000+ rows. 5,000 results in 1.1 seconds.
  3. Barand
    There should not be commas before "from" and before "right join" Queries are more efficient if you use explicit join syntax ie FROM A JOIN B ON ... instead of FROM A,B WHERE
  4. Barand
    I am guessing that the key to join on is supplier ref, as per your last post. SELECT * FROM tmp_BF INNER JOIN ps_product USING (supplier_referance) WHERE tmp_BF.wholesale_price <> ps_product.wholesale_price
  5. Barand
    Don't have column names containing spaces, use underscores. (If you really must, surround the name with backticks (note: not single quotes) eg `Completion Date`)
  6. Barand
    Just those that you want filled in
  7. Barand
    Specify the columns to receive the data INSERT INTO combinedList (col, names, go, here,) SELECT .....
  8. Barand
    If I understand correctly what you want, you need to call an AJAX request when the dropdown selection changes. Send the selected product id and get back the 3 product prices. Then set the price values of the 3 buttons from the returned data.
  9. Barand

    Joning tables

    Barand replied to yandoos's topic in MySQL Help

    Don't use "SELECT * ", especially when using joins. Specify the fields you want. Where you have the same field in two table (like pid) specify the table or alias with the field eg SELECT product.pid, ..... You only need to connect once per script unless you are connecting to two servers. real_escape_string() is for sanitizing input BEFORE using it in a query, not afterwards.
  10. Barand

    Joning tables

    Barand replied to yandoos's topic in MySQL Help

    use LEFT JOIN
  11. Barand
    Patience - we are not being paid by you so are not working on your time. Those are not particularly large tables, and, as Ch0cu3r said, there is probably something wrong somewhere else, perhaps a corrupted table. But you should consider redesigning the database so you are not having to read every record searching part of field. Use whole fields that can be indexed.
  12. Barand
    So it's timing out because it is too slow and you now want to make slower by adding inserts? Do you want to show all Stocks or just those that match Calendar? If you only want matching, use INNER join instead of LEFT, it's faster. BTW, how many rows in each of those tables?
  13. Barand
    Does the SName start with the value you are trying to match? If so, use CONCAT(s.SName, '%'). Do you have an index on c.SName? Have you tried running the query on its own with Workbench or phpMyAdmin?
  14. Barand
    Your first post seems to have the comments inside the while loop (comment for each question). Your last one doesn't. Which is it? If there is only one comment, Then just have "name='comments'" and storeit in the customer_feedback table, not the answer table. You will get the value from $_POST['comments']
  15. Barand
    The delete key should do it.
  16. Barand
    Have you tried SELECT s.SName, s.Symbol, c.`Primary Completion Date` FROM Stocks s LEFT JOIN Calendar as c ON c.SName LIKE CONCAT('%', s.SName, '%')
  17. Barand
    You should be joining on IDs (primary and foreign keys), not names anyway.
  18. Barand
    My profile : http://forums.phpfreaks.com/user/3105-barand/ Alternatively, you may want to model it like this, allowing multiple customer orders on one invoice or multiple invoices for an order.
  19. Barand
    Product to Price is 1 - Many. Your own requirements stated different prices for a product depending on customer type. There will also be different prices depending on date (old prices and current price (and maybe future dates for planned price increases) This enables you always to get the price that was in effect on a particular date. So if the price changed last week, orders/invoices from last month will reflect the price that applied then. Order to Invoice will 1 - 1 or Many. Normally you will raise an invoice for an order but on occasion a partial invoice may be raised then a second on completion of the remainder of the order. Again, overcome by applying from/to dates to changeable values like prices, tax rates etc. The query SELECT customer_id , cust_name , address , type_descrip FROM customers INNER JOIN customer_type USING (cust_type) WHERE customer_id = $customer"
  20. Barand
    Yes, you are using a relational database. Do not delete products. Instead add a "deleted" flag so they are removed from sale but descriptions are still available for historical data. Not only OK but essential, as the price is also dependent on customer type. Reseller - wouln't that just be another type of customer? From your brief description, I have identified the following tables (model attached)
  21. Barand
    You get them from the data posted from the form foreach ($_POST['answer'] as $qid => $choiceid) {
  22. Barand
    The comments fields would also require an array index, like the answer e.g. <textarea name="comment[$row['qID']]"></textarea> So foreach ($_POST['answer'] as $qid => $choiceid) { $comment = $_POST['comment'][$id]; // get the associated comment // insert feedbackid, qid, choiceid and comment into feedback_answer } You are saving customer name etc in the customer_feedback record. The related answers have the same feedback id. (see diagram ^^)
  23. Barand
    That's a lot of information that you are duplicating for every answer. Split off the answer data into a separate table +-------------------+ | customer_feedback | +-------------------+ | ID (PK) |---------+ | WorkOrderID | | | CustomerName | | +------------------+ | Org | | | feedback_answers | | Division | | +------------------+ | surveydate | +----<| feedback_id(PK) | | IPAddress | | qID (PK) | +-------------------+ | ChoiceID | | comments | +------------------+ and name = "answer[$row['qID']]"
  24. Barand
    This has to fairly general as we have no idea about the table you want to inrt into. First, your radio button names need to be something like "answer[$row['qID']]" Then when you process the form foreach ($_POST['answer'] as $id => $choice) { // insert id and choice into your answer table }
  25. Barand
    It's time to rtfm http://uk1.php.net/manual/en/function.array-unique.php
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.