Barand
-
Posts
24,566 -
Joined
-
Last visited
-
Days Won
822
Everything posted by Barand
-
Please help me add some code for my shopping cart
Barand replied to susanwiese's topic in PHP Coding Help
Instead of looping through the session array to see if pid already exists, why don't you make pid the key to the array element. Saves all that array searching and splicing eg <?php // check if in array if (isset($_SESSION['cart'][$pid])) { // it exists so increment the qty $_SESSION['cart'][$pid]['qty']++; } else { // add to cart $_SESSION['cart'][$pid] = array ('qty' => 1, 'engraving' => $engraving); } ?> -
This is your query 'SELECT * FROM ps_product WHERE ps_product.active = ' You need to use double quotes to enclose the query, not single quotes.
-
Your subquery is missing the word "SELECT" It also has "from workout_routines x, workout_machines y ON" instead of "from workout_routines x INNER JOIN workout_machines y ON" When you join from workout_results to subquery a, you don't specify what you supposed to be joining on? Try $q = "select r.*, a.* from workout_results r right join ( SELECT x.*, y.* from workout_routines x INNER JOIN workout_machines y on x.machine_id = y.machine_id WHERE x.user_id = '$userid' and x.routine_id = '$routine' and ) a on r.machine_id = a.machine_id and r.user_id = '$userid' and r.workout_date = $seldate and r.routine_id = '$routine' order by a.machine_seq";
-
Are sure you really want to match on a.player_id=b.exp_total? Seems strange to match an id against a total, or just bad column naming. If that is the case, why bother to select both columns if you know they are going to be the same in the results
-
Of course there is a way to convert it - that's what STR_TO_DATE() is doing. BTW, date column name will need the backticks - I just noticed you haven't got rid of the spaces as advised.
-
you would convert the date like this SELECT STR_TO_DATE('April 4th 2016', '%M %D %Y'); // --> 2016-04-04 So your query becomes this (you current query, BTW, searches the last 30 days, not the next 30) SELECT * FROM CleanedCalendar WHERE STR_TO_DATE(Completion Date, '%M %D %Y') BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY;
-
Populating dropdown menu with mysql data problem
Barand replied to bambinou1980's topic in Javascript Help
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); ?> -
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.
-
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
-
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
-
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`)
-
Just those that you want filled in
-
Specify the columns to receive the data INSERT INTO combinedList (col, names, go, here,) SELECT .....
-
Populating dropdown menu with mysql data problem
Barand replied to bambinou1980's topic in Javascript Help
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. -
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.
-
use LEFT JOIN
-
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.
-
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?
-
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?
-
How do I insert survey results into the database?
Barand replied to sonnieboy's topic in PHP Coding Help
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'] -
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, '%')
-
You should be joining on IDs (primary and foreign keys), not names anyway.
-
Help with designing my mysql tables before coding in PHP
Barand replied to bambinou1980's topic in MySQL Help
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.
-
Help with designing my mysql tables before coding in PHP
Barand replied to bambinou1980's topic in MySQL Help
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"
