Barand
-
Posts
24,433 -
Joined
-
Last visited
-
Days Won
807
Posts posted by Barand
-
-
On 1/12/2020 at 9:21 PM, Barand said:
That's because, by default, MySQL will not run multiple queries.
-
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 }
-
13 hours ago, Barand said:
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.
-
You have the opening parenthesis after "on" but you are missing the closing one
-
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 ")"
-
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?
-
34 minutes ago, narutofan said:
so was my approach right when it comes to stored procedure.
No
-
3 minutes ago, narutofan said:
as one of the eldest members you shouldn't be making fun when ppl are learning
No so much making fun of you but rather showing the folly of your approach.
QuoteReductio Ad Absurdum
In logic, reductio ad absurdum (Latin for "reduction to absurdity"), also known as argumentum ad absurdum (Latin for "argument to absurdity"), apagogical arguments, negation introduction or the appeal to extremes, is a form of argument that attempts to establish a claim by showing that the opposite scenario would lead to absurdity or contradiction. It can be used to disprove a statement by showing that it would inevitably lead to a ridiculous, absurd, or impractical conclusion, or to prove a statement by showing that if it were false, then the result would be absurd or impossible. Traced back to classical Greek philosophy in Aristotle's Prior Analytics (Greek: ἡ εἰς τὸ ἀδύνατον ἀπόδειξις, lit. 'demonstration to the impossible', 62b), this technique has been used throughout history in both formal mathematical and philosophical reasoning, as well as in debate.
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.
-
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
-
4 hours ago, narutofan said:
After changing the query to this i get the following error code:
SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columnsHere'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.
-
9 minutes ago, narutofan said:
because its easier
LOL - yes, it's evident that you find it so much easier.
9 minutes ago, narutofan said:my tables have some problems for joined queries
Seems like you really need to fix those problems - they won't go away on their own.
9 minutes ago, narutofan said:plus i can get the values in two arrays
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.
-
Why a stored procedure?
-
8 hours ago, narutofan said:
@Barand can you help me with this. can you point where i'm going wrong.
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.
-
6 hours ago, Barand said:
What would you like the result to look like?
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
-
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)");
2 hours ago, narutofan said:now i get results from table updates and not from group_updates
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)
-
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"
-
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.
-
Show me how to load those data images into a test database so I can check out the subqueries and I'll be glad to help.
-
try
<?php require 'db_inc.php'; $db = pdoConnect(); // function defined in db_inc.php $search = $_GET['search'] ?? ''; $tdata = ''; $stmt = $db->prepare("SELECT ID , GAME , PLATFORM , Owned FROM games WHERE GAME LIKE ? "); $stmt->execute([ "$search%" ]); foreach ($stmt as $row) { $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>
-
Try incrementing the counter at the end of the loop.
Also no need for all the duplicate code. All you need is to set a variable
$suc = $counter % 2 == 1 ? 'success' : '';
E.G.
$counter = 0; foreach ($res as $row) { $suc = $counter % 2 == 1 ? 'success' : ''; $tdata .= "<tr class='$suc'> <td>{$row['firstname']}</td> <td>{$row['lastname']}</td> </tr>"; $counter++; }
then
<table> <?=$tdata?> </table>
-
The more information a query retrieves the slower it will run. In the code below all you want is a single count yet you retrieve every column in every record.
// find number of rows in the db $query = "SELECT * FROM menu_update WHERE special_of_theday = 1 AND restaurant_type = '".$selected_restaurant_type."' "; $query_result = mysqli_query($connection, $query); $row_count = mysqli_fetch_array($query_result); $total_rows = mysqli_num_rows($query_result);
Far more efficient to
$query = "SELECT COUNT(*) as total FROM menu_update WHERE special_of_theday = 1 AND restaurant_type = '$selected_restaurant_type' "; $query_result = mysqli_query($connection, $query); $row = mysqli_fetch_array($query_result); $total_rows = $row['total'];
-
It would appear from the code that you would always have links
1 2 3 4 ... N
except when you are on the last page. You show the links on this condition...
if ($page_number != $expected_number_of_pages)
Do you, perchance, only have two pages at present? And do you mean $page_number or do you mean $current_page (confusing names)
-
Your misleading choice of variables names no doubt contributed to the problem.
There are other things could be improved too
- Use prepared queries instead of putting variables directly into the sql query strings. (I would also recommend PDO in preference to mysqli)
- use css classes instead of repetitive inline style definitions
- you are using deprecated html markup code (eg <tr bgcolor = "#cccccc">)
- you use a lot of unnecessary concatenation when building strings - a common source of errors.
-
1
-
In your pagination loop you set all the links to the same page number!
Should be
$data = array( 'page' => $current_page, <-- CHANGED 'country' => $cleaned_current_page_country );
-
1
-
Don’t know how to get values from stored procedure
in PHP Coding Help
Posted
One that does not involve a stored procedure