Barand
-
Posts
24,420 -
Joined
-
Last visited
-
Days Won
805
Posts posted by Barand
-
-
Does this come close?
SELECT a.order_id , o.order_date , a.user_id , u.username , status FROM application a INNER JOIN `order` o USING (order_id) LEFT JOIN user u USING (user_id) LEFT JOIN ( SELECT order_id FROM application WHERE status IS NOT NULL AND status <> 1 ) as stat USING (order_id) WHERE stat.order_id IS NULL; +----------+------------+---------+----------+--------+ | order_id | order_date | user_id | username | status | +----------+------------+---------+----------+--------+ | 1 | 2019-06-01 | | | | | 2 | 2019-07-01 | 1 | Curly | 1 | | 2 | 2019-07-01 | 2 | Larry | 1 | | 2 | 2019-07-01 | 3 | Mo | 1 | +----------+------------+---------+----------+--------+
-
On 9/6/2019 at 9:56 AM, SH1989 said:
and then the status is changed to 3 when it's accepted.
4 hours ago, SH1989 said:When the application has been accepted, the status is updated to 4
Which is it?
Do know what's going on with your application?
-
I am guessing here so correct me if I'm wrong, but I need to get you started.
If you had this
USER APPLICATION ORDER +---------+----------+ +----------------+----------+---------+--------+ +----------+------------+ | user_id | username | | application_id | order_id | user_id | status | | order_id | order_date | +---------+----------+ +----------------+----------+---------+--------+ +----------+------------+ | 1 | Curly | | 1 | 1 | NULL | NULL | | 1 | 2019-06-01 | | 2 | Larry | | 2 | 2 | 1 | 1 | | 2 | 2019-07-01 | | 3 | Mo | | 3 | 2 | 2 | 1 | | 3 | 2019-07-15 | | 4 | Peter | | 4 | 2 | 3 | 1 | | 4 | 2019-07-25 | | 5 | Paul | | 5 | 3 | 4 | 2 | | 5 | 2019-08-01 | | 6 | Mary | | 6 | 3 | 5 | 1 | | 6 | 2019-01-02 | +---------+----------+ | 7 | 3 | 6 | 1 | +----------+------------+ | 8 | 4 | 1 | 2 | | 9 | 5 | 2 | 2 | | 10 | 6 | 4 | 4 | +----------------+----------+---------+--------+then what output would you want to see?
-
If anything, it is now more confusing.
Your original post referred to "Applications" and "Users" tables. You also had ...
On 9/6/2019 at 9:56 AM, SH1989 said:WHERE Order = '1'
... but this sample data has no "order" column.
This post refers to "Applications" and "Orders" tables
What does the entity relationship between these three tables look like? (Knowing what the data looks like and how it is related is a great to help to writing correct queries.)
And you still haven't told us what you want to see on the screen as a result of the query.
-
Here's a shorter and faster version of the randkey() function
function randkey2($j=10) { $bag = array_merge(range('a','z'), range('A', 'Z'), range(1,9)); // put chars in a bag shuffle($bag); // shake the bag return join('', array_slice($bag,0,$j)); // pull out $j chars }
Profiler results calling both functions (yours and mine) for each of 158 records showing total time spent in each function
-
I would go for
+----------+ +-------------------+ +----------------+ | member | | membership_plan | | site_feature | +----------+ +-------------------+ +----------------+ \ / \ / \ / \ / +--------------+ +--------------+ | subscription | | entitlement | +--------------+ +--------------+ -
You are overthinking it using variable variables.
if you have ...
$qb1 = 'John'
then ${$qb1) is looking for tha contents of $John (which doesn't exist and hnce the blank name). You need to turn your error reporting on.
Use
getplayer($qb1);
-
So long as the name is meaningful, describing the entity or relationship contained in the table, then that's all that matters.
-
If you do an update without a WHERE clause ...
UPDATE atable SET acol = 'X'
then every row gets the same update.
Here's how I'd do it, selecting the ids of the records and allocating a key to each in an array. Then updating the records with their new values from the array
$data = []; $res = $db->query("SELECT customer_id FROM customer ORDER BY customer_id"); foreach ($res as $r) { $data[] = sprintf("(%d, '%s')", $r['customer_id'], randkey()) ; } /* WE NOW HAVE AN ARRAY OF RECORDS WITH EACH ID AND ITS ASSOCIATED RANDKEY WHICH WE CAN USE TO UPDATE THE TABLE. E.G. Array ( [0] => (1, '8abNT88U4c') [1] => (2, 'JZ0lIh958E') [2] => (3, '1s62XVkZQX') [3] => (4, '1j0SBRCOey') [4] => (5, 'DIvq8eBkL7') [5] => (6, 'IJWG8Vdevz') [6] => (7, 'Vv7OrJPw9t') [7] => (8, 'AwG8l87PcQ') [8] => (9, 'BG0cQLuybK') [9] => (10, 'Mv27T3oajx') ) */ // UPDATE ALL RECORDS WITH SINGLE QUERY $db->exec("INSERT INTO customer (customer_id, my_new_col) VALUES " . join(',', $data) . " ON DUPLICATE KEY UPDATE my_new_col = VALUES(my_new_col) ");
-
My guesses would be
- A register of Dukes, Duchesses, Earls, Countesses, Barons and Knights and any other titled personnel
- A table defining access rights to products/services for users
- A table defining leave entitlement for employees
-
... ORDER BY start_date DESC, RAND()
-
12 hours ago, SaranacLake said:
How did you get the spacing to work on the web?
Monospaced font (courier) between <pre>..</pre> tags does the job, as does using the <> button in this forum.
12 hours ago, SaranacLake said:although the columns you have under User shouldn't be there
Oops| Forgot to edit that one after copy/pasting.
-
1
-
-
Something like this, perhaps
USER ********* BOOK - user_id ---+ ********* - metadata fields | +----- - book_id ------+ - chapter_no | | - metadata fields | - chapter_title | | - title | - chapter_content | | - author | - footnotes | | - preface | | | | | | | | | | | SUBSCRIPTION | | CHAPTER | *********** | | ********* + - id | | - chapter_id +---< - user_id | +--------< - book_id - book_id >----+ - metadata fields - chapter_no - chapter_title - chapter_content - footnotes -
1 hour ago, SaranacLake said:
If so, does taking that approach even make sense - to put one chapter in one column-row?
I was giving you an option in response to your earlier comment, viz...
14 hours ago, SaranacLake said:That works for articles that are maybe 1-5 pages, but I'm not sure that there is a MySQL datatype that would hold 30 pages of HTML/content.
I wasn't neccessarily recommending that approach. It depends on how you want to serve up the content to the users. If they a reading online, you may decide to serve it up page by page, storing individual page records (TEXT column).
Alternatively, store each book in an offline zip file (each chapter being a separate file inside, maybe) and the user can download the whole zipped book.
-
3 hours ago, kicken said:
I think I saw something recently about it having a json datatype,
So does MySQL as from version 5.7
-
Let's assume each of your pages contains 50 lines of 80 characters, that is 4,000 characters per page (12,000 bytes if utf8). Also that the largest chapter is 50 pages. This would give a requirement for each chapter of 600,000 bytes. That's too big for a TEXT type column (max 65000) but a MEDIUMTEXT type would have no problems (with maximum capacity of 16,777,216)
Your maximum packet size is probably 4,194,304 which would constrain your chapters to a maximimum of around 340 pages (enough for some books).
-
How are you passing the "name" value to that page?
-
Not even close. This code...
$product_details = "SELECT * FROM product WHERE product_id=".$_GET['product_id']; $prepare = $connect->prepare($product_details); $prepare->execute();...would embed any SQL injection code contained in the GET into the query which would then be executed. (Just as an unprepared query would)
In the correct version the injection code would only be treated as data and not part of the SQL code.
-
1
-
1
-
-
5 minutes ago, gw1500se said:
I didn't think there was a difference between the 2
The principle behind prepared statements is the separation of user-provided data from the query SQL code. This is accomplished by putting placeholders in the query and then binding parameters to those placeholders when executing
-
56 minutes ago, gw1500se said:
Yep
Not if $product_details is still as posted earlier IE
1 hour ago, mahenda said:$product_details = "SELECT * FROM product WHERE product_id=".$_GET['product_id'];
You need
$product_details = "SELECT * FROM product WHERE product_id = ?"; $prepare = $connect->prepare($product_details); $prepare->execute( [ $_GET['product_id'] ] ); -
Can you provide the following
- Your table structures
- Some sample data which is representative of the problem
- The output you want from that sample data
-
The whole point of using prepared statements with parameters is to avoid having to sanitize your inputs against SQL injection.
Adding some attributes to your PDO connection also helps.
- ATTR_EMULATE_PREPARES - this is "true" by default so set it "false" to stop it emulating prepared statements
- ATTR_ERRMODE - set this so that exceptions are thrown automatically and you don't have to keep checking for errors at every step
- ATTR_DEFAULT_FETCH_MODE - set this so you don't have to specify every time
Why are you using "printf()" when you aren't formatting anything? Simple "echo" would suffice.
1 hour ago, SheenLim08 said:$sqlConnection->
that 3rd line in your try block might be causing problems.
Try a simpler approach...
require_once './login.php'; if ($_SERVER['REQUEST_METHOD']=='POST' && isset($_POST['txtISBN']) ) { $sqlConnection = new PDO($mysqlDSN, $mySQLUsername, $mySQLUserPass); // SET PDO ATTRIBUTES AFTER CONNECTING $sqlConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sqlConnection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $sqlConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $sqlStatement = $sqlConnection->prepare("SELECT * FROM classics WHERE isbn = :isbnSearchID"); $parameters = [ ":isbnSearchID" => $_POST['txtISBN') ]; if ($sqlStatement->execute($parameters)) { $result = $sqlStatement->fetch(PDO::FETCH_ASSOC); if ($result) { printf("<br />"); printf("ISBN: " . sanitiseForHTML($result['isbn']) . "<br />"); printf("Author: " . sanitiseForHTML($result['author']) . "<br />"); printf("Book: " . sanitiseForHTML($result['title']) . "<br />"); printf("Category: " . sanitiseForHTML($result['category']) . "<br />"); printf("Year: " . sanitiseForHTML($result['year']) . "<br />"); printf("Type: " . sanitiseForHTML($result['type']) . "<br />"); } else { printf("<p>The ISBN you are looking for has not been found!</p>"); printf("<a href=\"phpsearchbook.php\">Go back to Search page.</a>"); } } else { printf("<p>There was an issue during the search query!</p>"); printf("<a href=\"phpsearchbook.php\">Go back to Search page.</a>"); } }
-
First thing you should do is get a keyboard that that has return and tab keys. That will enable you to format your code into separate lines with indents so it is understandable.
When you've done that, repost your code using a code box ("<>" button in the toolbar).
If it's readable there's a chance someone might read it.
-
1
-
-
My 0.02 worth...
Why are you "denormalizing" your data by creating several objects all with the same id and color code. Would it not be more sensible (and a lot easier) to create a merged data set like this...
<PRODUCTINFORMATION> <PRODUCTS> <PRODUCT> <PRODUCT_NUMBER>53-03</PRODUCT_NUMBER> <PRODUCT_PRINT_ID>42</PRODUCT_PRINT_ID> <PRODUCT_NAME>ProductFirst</PRODUCT_NAME> <COLOR_CODE>03</COLOR_CODE> <PRINTING_POSITION> <ID>TOP BOX</ID> </PRINTING_POSITION> <PRINTING_TECHNIQUES> <PRINTING_TECHNIQUE> <ID>DL</ID> </PRINTING_TECHNIQUE> <PRINTING_TECHNIQUE> <ID>L2</ID> </PRINTING_TECHNIQUE> <PRINTING_TECHNIQUE> <ID>P4</ID> </PRINTING_TECHNIQUE> </PRINTING_TECHNIQUES> </PRODUCT> </PRODUCTS> </PRODUCTINFORMATION>
Below is my solution to your original quest using SimpleXML. Hava a go at it on your own as requinix suggested first then peek only if you really get stuck
$xmlA = simplexml_load_file('A.xml'); // product info $xmlB = simplexml_load_file('B.xml'); // printing info // create empty output xml object $xmlC = new simpleXMLElement("<PRODUCTINFORMATION> </PRODUCTINFORMATION>"); $products = $xmlC->addChild("PRODUCTS"); // process file A foreach ($xmlA->PRODUCTS->PRODUCT as $proda) { $prodno = (string)$proda->PRODUCT_NUMBER; $prtid = (string)$proda->PRODUCT_PRINT_ID; $prodname = (string)$proda->PRODUCT_NAME; $color = (string)$proda->COLOR_CODE; // find related print info xml from xml file B based on PRODUCT_PRINT_ID $prodarr = $xmlB->xpath("PRODUCTS/PRODUCT[PRODUCT_PRINT_ID='$prtid']"); $prodb = $prodarr[0]; $prtposid = (string)$prodb->PRINTING_POSITIONS->PRINTING_POSITION->ID; // build the output xml $prodnew = $products->addChild('PRODUCT'); $prodnew->addChild('PRODUCT_NUMBER', $prodno); $prodnew->addChild('PRODUCT_PRINT_ID', $prtid); $prodnew->addChild('PRODUCT_NAME', $prodname); $prtposns = $prodnew->addChild('PRINTING_POSITIONS'); foreach ($prodb->PRINTING_POSITIONS->PRINTING_POSITION->PRINTING_TECHNIQUE as $tech) { $posnew = $prtposns->addChild('PRINTING_POSTION'); $posnew->addChild('ID', $prtposid); $postech = $posnew->addChild('PRINTING_TECHNIQUE'); $postech->addChild('ID', (string)$tech->ID); $posnew->addChild('COLOR_CODE', $color); } } file_put_contents('C.xml', $xmlC->asXML() ); // write output to file C /* C.XML produced by above code ****************************** <PRODUCTINFORMATION> <PRODUCTS> <PRODUCT> <PRODUCT_NUMBER>53-03</PRODUCT_NUMBER> <PRODUCT_PRINT_ID>42</PRODUCT_PRINT_ID> <PRODUCT_NAME>ProductFirst</PRODUCT_NAME> <PRINTING_POSITIONS> <PRINTING_POSTION> <ID>TOP BOX</ID> <PRINTING_TECHNIQUE> <ID>DL</ID> </PRINTING_TECHNIQUE> <COLOR_CODE>03</COLOR_CODE> </PRINTING_POSTION> <PRINTING_POSTION> <ID>TOP BOX</ID> <PRINTING_TECHNIQUE> <ID>L2</ID> </PRINTING_TECHNIQUE> <COLOR_CODE>03</COLOR_CODE> </PRINTING_POSTION> <PRINTING_POSTION> <ID>TOP BOX</ID> <PRINTING_TECHNIQUE> <ID>P4</ID> </PRINTING_TECHNIQUE> <COLOR_CODE>03</COLOR_CODE> </PRINTING_POSTION> </PRINTING_POSITIONS> </PRODUCT> </PRODUCTS> </PRODUCTINFORMATION> ***************************************************************/
-
1
-
Help - MySQL Select Query
in MySQL Help
Posted · Edited by Barand
What about status=2 (offered). They seem to have disappeared from the scene.
Also NULL no longer seems to be an option and status 3 evaporated comlpletely a while ago.
(If this were a murder investigation you would be #1 suspect - every account you've given has been different, leaving me to try an hit a moving target.)
When you have finally decide what you want, adapt the query I gave you a couple of posts ago - it should be close.
Good luck.