Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,566

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    LINE 82 - "$stmt" should be "$ins_i" (I missed that one)
  2. Barand
    Fingers crossed here - it's untested!
  3. Barand
    IMHO it's easier just to do both inserts in the same loop as you always want one of each. ////////////////////////////////////// // menu item and connection insert // ////////////////////////////////////// $ins_i = $conn->prepare(' INSERT INTO ssm_menu_items (menu_item_name) VALUES (?); '); $ins_c = $conn->prepare(' INSERT INTO ssm_menu_connection (menu_id, menu_item_id) VALUES (?,?) '); $ins_i->bind_param('s',$nmItem); $ins_c->bind_param('ii', $menuInsId, $menuItmInsId); foreach ($_POST['newMenuItem'] as $nmItem) { $ins_i->execute(); $menuItmInsId = $stmt->insert_id; $ins_c->execute(); }
  4. Barand
    As I said, you can not execute queries like this ... $sql = "select * FROM updates where `author` ='".$friend."' order by time desc limit 1 ;"; $sql .= "select * FROM group_posts where `author_gp` ='".$friend."' order by pdate desc limit 1"; … where you are attempting to put multiple queries in a single string. However I was wrong regarding the stored procedure, for which I apologise. After further experimentation, I have found that several queries inside a procedure will work... CREATE PROCEDURE `timeline`( IN theauthor VARCHAR(255) ) BEGIN SELECT update_id , account_name , user_id_u , author , type , time , title , update_body FROM updates WHERE author = theauthor LIMIT 5; SELECT gp_id , gname , author_id , author_gp , type , pdate , title , data FROM group_posts WHERE author_gp = theauthor LIMIT 5; END To process (PDO) ... $results = $db->query("CALL timeline($friend)"); do { $timeline_data[] = $results->fetchAll(); } while ($results->nextRowset()); //view results echo '<pre>', print_r($timeline_data, 1), '</pre>'; Alternatively, there is the function you last posted (rewritten) function totalprocedures($conn,$v) { $results = []; $sqlArray = [ [ "query" => "SELECT * FROM updates WHERE `author` = ? ORDER BY time DESC LIMIT 1 ", "bindvalue" => [$v] ], [ "query" =>"SELECT * FROM group_posts WHERE `author_gp` = ? ORDER BY pdate DESC LIMIT 1", "bindvalue" => [$v] ] ]; foreach ($sqlArray as $qry) { $stmt=$conn->prepare($qry["query"]); $stmt->execute($qry['bindvalue']); $results[] = $stmt->fetchAll(); } return $results; }
  5. Barand
    If I haven't said it to you already, I recommend you change to PDO. It's more streamlined and easier to use than mysqli. (As you can see, it took twice as much code to process the query)
  6. Barand
    because you needed a PDO connection and that is what it does Here's a mysqli version <?php $dbServername = "localhost"; $dbUsername = "root"; $dbPassword = ""; $dbName = "csv_db"; $conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName); $search = $_GET['search'] ?? ''; $tdata = ''; $stmt = $conn->prepare("SELECT ID , GAME , PLATFORM , Owned FROM games WHERE GAME LIKE ? "); $srchStr = "$search%"; $stmt->bind_param('s', $srchStr); $stmt->execute(); $row = []; $stmt->bind_result($row[0],$row[1],$row[2],$row[3]); while ($stmt->fetch()) { $tdata .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n"; } ?> <!DOCTYPE html> <html> <head> <title>Sample Search</title> <style type='text/css'> body { font-family: calibri, sans-serif; font-size: 11pt;} header{ background-color: black; color: white; padding: 16px; } form { padding: 16px; } table { width: 75%; margin: 30px auto; border-collapse: collapse; } th { background-color: black; color: white; padding: 8px; } td { padding: 4px 8px;} </style> </head> <body> <header> <h1>Sample</h1> </header> <form method='GET'> Search for: <input type="text" name="search" value="<?=$search?>" size="40"> <input type="submit" name="btnSub" value="Search"> </form> <div> <?php if ($search) { ?> <table border='1'> <thead> <tr> <th>ID</th> <th>GAME</th> <th>PLATFORM</th> <th>OWNED</th> </tr> </thead> <tbody> <?=$tdata?> </tbody> </table> <?php } ?> </div> </body> </html>
  7. Barand
    Perhaps you should also have read the rest of that reply. P.S. +-----------------------------------------------------------------------------+ | Multiple queries | +----------------------------------------+------------------------------------+ | CONS | PROS | +----------------------------------------+------------------------------------+ | | | | 1 - more complicated to use | | | | Can't think of any | | 2 - they open wide the door to some | significant advantages | | seriously catastrophic hacks | | | | | +----------------------------------------+------------------------------------+
  8. Barand
    One that does not involve a stored procedure
  9. Barand
  10. Barand
    If you are still determined to use a single stored procedure then you need a single query, therefore use a union. Note that I had to rearrange the order of your column selections in each part so that like types were in the same columns stored procedure... CREATE PROCEDURE `timeline`( IN `friend` varchar(255), IN `session_id` varchar(255) ) BEGIN SELECT * FROM ( SELECT 'U' as source , update_id as up1 , account_name as acc , user_id_u as uid , author as auth , type , time , title as tit1 , update_body as u_body FROM updates as up WHERE author IN (friend,session_id) LIMIT 5 ) a UNION SELECT * FROM ( SELECT 'GP' as source , gp_id , gname , author_id , author_gp , type , pdate , title , data FROM group_posts as gp WHERE author_gp IN (friend,session_id) LIMIT 5 ) b ORDER BY time DESC; END To use $res = $db->query("CALL timeline($friend, $session_id)"); while ($row = $res->fetch_assoc()) { // process row }
  11. Barand
  12. Barand
    You have the opening parenthesis after "on" but you are missing the closing one
  13. Barand
    Puzzling indeed. Does locating the element that does have data-userid=8 help to clarify what's going on? BTW, I think the closing line of your event handler is missing the final ")"
  14. Barand
    Have you tried using your browser's developer tools to step through the JS code and inspect the variables to find the point of failure?
  15. Barand
    No
  16. Barand
    No so much making fun of you but rather showing the folly of your approach. I've suggested a couple of times in this thread that you just use two normal queries. You could even use a single query with a union but you said you wanted the two results kept separate for some reason. Yet despite any advice (which you always have a habit of ignoring) you insist on this "easier" stored procedure approach.
  17. Barand
    I haven't waded through all the code but I wondered, from the symptoms, if the cause is similar to this situation. Consider this simple html script <!DOCTYPE html> <html> <head> <title>Sample Search</title> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type='text/javascript'> $().ready( function () { $("button").click( function() { $("#main").append("<button>Click to add new button</button><br>") }) }) </script> </head> <body> <header> <h1>Sample</h1> </header> <div id="main"> <button>Click to add new button</button> <button>Click to add new button</button> <button>Click to add new button</button> <br><br> </div> </body> </html> Very simple - buttons which, when clicked, add another button for you to click. You click a button and, lo, another appears. Click a new button - nothing! Click an original button and another appears. The on-click event handler was created just after the form loaded and so was only applied to button objects that existed then. New buttons are not assigned the handler. I was wondering if a similar thing is affected your script. You only get values from the stage the form loaded
  18. Barand
    Here's a simpler example to illustrate your problem mysql> select id, name INTO @a FROM locations; ERROR 1222 (21000): The used SELECT statements have a different number of columns The query is trying to select 2 columns into 1 variable so it throws that error. Aha! so all we have to do is put the 2 columns into 2 variables? mysql> select id, name INTO @a, @b FROM locations; ERROR 1172 (42000): Result consisted of more than one row Alas, you can't do that with a query returning many rows, so... mysql> select id, name INTO @a, @b FROM locations LIMIT 1; Query OK, 1 row affected (0.00 sec) mysql> select @a, @b; +------+-----------------+ | @a | @b | +------+-----------------+ | 1 | Test Location 1 | +------+-----------------+ Now we're getting somewhere - all you need to do is create a stored procedure and call it 10 times, each time selecting a different single row in to 7 variables. Then after you've finished, put all those 70 variables into your timeline. As you said, much easier than using two normal queries.
  19. Barand
    LOL - yes, it's evident that you find it so much easier. Seems like you really need to fix those problems - they won't go away on their own. As you can with two queries, one for each table. But I guess you would find that so much more difficult! [EDIT] If you putting them all in the one timeline, why would want to keep them separate? I would have thought you would want them all merged an sorted by date.
  20. Barand
    Why a stored procedure?
  21. Barand
    How can I? I have no idea what you are trying to do. I can see the method you are attempting to achieve your objective but I haven't clue about what that objective is, or why you have chosen this method.
  22. Barand
    Although you never actually confirmed your expected output, this is what I think you want SELECT p.productName , p.productCode , pi.current_inventory , pi.transaction_date , l.name FROM product_locations pl JOIN products p ON pl.product_id = p.productCode JOIN locations l ON pl.location_id = l.id JOIN product_inventory pi ON pl.id = pi.product_location_id JOIN ( SELECT pl.product_id , MAX(transaction_date) as date FROM product_locations pl JOIN product_inventory pi ON pl.id = pi.product_location_id WHERE transaction_date < '2020-01-03 23:59:59' AND pl.location_id = 1 GROUP BY pl.product_id ) latest ON latest.product_id = pl.product_id AND pi.transaction_date = latest.date; +---------------------------------------+-------------+-------------------+---------------------+-----------------+ | productName | productCode | current_inventory | transaction_date | name | +---------------------------------------+-------------+-------------------+---------------------+-----------------+ | 1969 Harley Davidson Ultimate Chopper | S10_1678 | 25 | 2020-01-03 11:27:35 | Test Location 1 | | 1952 Alpine Renault 1300 | S10_1949 | 11 | 2020-01-01 12:23:11 | Test Location 1 | +---------------------------------------+-------------+-------------------+---------------------+-----------------+ Subquery finds latest date for each product and uses that date to find matching inventory record
  23. Barand
    Why are you preparing a query that is a call to a stored procedure? The whole point of preparing queries is to isolate the data from the query code. Stored procedures already do that (the IN clauses are the same as binding parameters in a prepared query). Just use $result = $this->db->query("CALL timeline($friend_name, $session_id)"); That's because, by default, MySQL will not run multiple queries. You can turn on multiple client queries but that will probably give you even more problems. Split it into two calls/ stored procedures (if you feel it really must be stored procedures)
  24. Barand
    OK, so it looks like this? The key to your problem lies in that join between your other tables and the subquery Your query results +---------------------------------------+-------------+-------------------+---------------------+-----------------+------+ | productName | productCode | current_inventory | transaction_date | name | id | +---------------------------------------+-------------+-------------------+---------------------+-----------------+------+ | 1969 Harley Davidson Ultimate Chopper | S10_1678 | 25 | 2020-01-03 00:00:00 | Test Location 1 | 1 | | 1952 Alpine Renault 1300 | S10_1949 | NULL | NULL | Test Location 1 | 3 | +---------------------------------------+-------------+-------------------+---------------------+-----------------+------+ | | +-------------------------------------------- your join -------------------------------------------------+ | | | Subquery results +---------------------+---------------------+-------------------+ | product_location_id | transaction_date | current_inventory | +---------------------+---------------------+-------------------+ | 1 | 2020-01-03 00:00:00 | 25 | +---------------------+---------------------+-------------------+ Adding pl.id to your query's field list illustrates it. Your two records have values 1 and 3. Because you LIMIT 1 in the subquery it's going to give only one record, which matches the "1"
  25. Barand
    What would you like the result to look like?. Then we know what you are trying do. Your inventory data is only by location and date - there is no product id - so it's hard to get values for each product. You have stock totals by product and location but I see no mention of stock in your query.
×
×
  • 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.