Jump to content

Barand

Moderators
  • Posts

    24,420
  • Joined

  • Last visited

  • Days Won

    805

Posts posted by Barand

  1. 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.

  2. 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 |
    +----------+------------+---------+----------+--------+

     

  3. 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?

  4. I am guessing here so correct me if I'm wrong, but I need to get you started.

    If you had this

    image.png.4ce97b03a540db4d53a7d70cc712ba60.png

    
    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?

  5. 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.

  6. 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

    image.png.0fbac360d71407f0a619af3fc6cb1cd2.png

  7. I would go for

                +----------+                  +-------------------+                 +----------------+
                |  member  |                  |  membership_plan  |                 |  site_feature  |
                +----------+                  +-------------------+                 +----------------+
                           \                /                     \                /
                            \              /                       \              /
                            +--------------+                       +--------------+
                            | subscription |                       |  entitlement |
                            +--------------+                       +--------------+
    

     

  8. 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)
              ");

     

  9. My guesses would be

    1. A register of Dukes, Duchesses, Earls, Countesses, Barons and Knights and any other titled personnel
    2. A table defining access rights to products/services for users
    3. A table defining leave entitlement for employees
  10. 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.

    • Like 1
  11. 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

     

  12. 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.

  13. 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).

  14. 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.

    • Like 1
    • Great Answer 1
  15. 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

  16. 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'] ] );

     

  17. 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>");
    
        }
            
    
    }

     

  18. 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>
    
    ***************************************************************/

    • Great Answer 1
×
×
  • 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.