Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,433

  • Joined

  • Last visited

  • Days Won

    807

 Content Type 

Posts posted by Barand

    Image X and Y for watermark

    in PHP Coding Help

    Posted · Edited by Barand

    A few notes about text bounding boxes which, I hope, will help in precise placement of your text.

    Suppose I have the text string "The lazy fox" which I want to display using 150pt Vivaldi . My image is 4896 x 3672 and I want the text placed at the bottom right but 250 pixels from the edges of the image. 

    $box = imagettfbbox(150,0,'c:/windows/fonts/vivaldii.ttf','The lazy fox');

    gives this array of coordinates of the four corners 

    $box = Array
(
    [0] => 23
    [1] => 55
    [2] => 871
    [3] => 55
    [4] => 871
    [5] => -140
    [6] => 23
    [7] => -140
)

    You may wonder why it can't just give a rectangle from (0,0) to (width, height) to make sizing simple, but there is extra information to be extracted from the array

    image.png.e620bd88fea8f03476a0eec290a9ff09.png

    • Text width = (871 - 23) = 848
    • Text height = 55 - (-140) = 195
    • The baseline will be 140px from the top
    • The text is offset 23 px to the right.

    My text, therefore, will be in a rectangle 848 x 195 positioned 250 px from right and bottom edges.

    The top left x coord of the rectangle will be (4896 - 250 - 848) = 3798 and top left y coord will be (3672 - 250 - 195) = 3227.

    However, to land the text precisely into this area we position it on the baseline and at the required x offset, ie (3798 - 23 , 3227 + 140) = (3775, 3367).

    image.png.32aa3308be6665f90df69b948c5df419.png

    I use a simple custom function to assist with this process 

    function metrics($font, $fsize, $str) 
{
    $box = imagettfbbox($fsize, 0, $font, $str);
    $ht = abs($box[5] - $box[1]);
    $wd = abs($box[4] - $box[0]);
    $base = -$box[5];
    $tx = -$box[0];
    return [ 'width' => $wd,
             'height' => $ht,
             'ascent' => $base,
             'offsetx' => $tx
           ];
}

$box = metrics ('c:/windows/fonts/vivaldii.ttf', 150, 'The lazy fox');

$box = Array
(
    [width] => 848
    [height] => 195
    [ascent] => 140
    [offsetx] => -23
)

     

    • Great Answer 3

    PHP POLL

    in PHP Coding Help

    Posted

    I'd start with the data. Hard-coding the questions and answers into the program isn't the best way. If you don't want to use a database then IMO, for this type of application, an ini file format is a good, easily editable and easily processed option.

    Something like this, which gives you simple yes/no or multiple choice options... 

    [1]
Q="Is Sydney the capital of Australia?"
A="Yes"
B="No"
Answer=B

[2]
Q="What is the next number in this series 1, 3, 5, 7, 11, 13, ... ?"
A=15
B=17
C=19
Answer=B

[3]
Q="How high is Mount Everest?"
A=8,254m
B=8,592m
C=8,848m
D=9,121m
Answer=C

[4]
Q="When was Albert Einstein born?"
A=1879
B=1899
C=1909
D=1919
Answer=A

[5]
Q="How many symphonies did Mozart write?"
A=40
B=41
C=43
D=45
Answer=B

[6]
Q="What was the U.S. President's name in 2002?"
A="Bill Clinton"
B="George W Bush"
C="Barack Obama"
D="Donald Trump"
Answer=D

    That should give you a starting point. (Hint: https://www.php.net/parse_ini_file)

    Problem using Max and Count

    in Microsoft SQL - MSSQL

    Posted

    This part of the query ... 

             (
            SELECT wo_number
                 , MAX(sequence) as sequence
            FROM wct 
            GROUP BY wo_number
         ) latest

    is a table subquery with alias "latest". It is essentially a dynamic temporary table called "latest" containing the latest sequence number for each wo_number .

    We then join the wct table to this temporary table on (wo_number, sequence) to find the matching wct record and, in particular, its response value. It just selects those that match that latest sequence and response id PMU.

    The column "latest.sequence" is just there so I could check it was pulling the expected values.

     

    These next two statements of yours seem contradictory:

    4 hours ago, utspam said:

    Goal: List only work orders where

    14 minutes ago, utspam said:

    Frankly, I wasn't necessarily concerned with linking the wo table

     

    Problem using Max and Count

    in Microsoft SQL - MSSQL

    Posted

    Data 

    TABLE: work_order                  TABLE: wct;
+-----------+--------------+       +-----------+----------+----------+--------+
| wo_number | wo_desc      |       | wo_number | sequence | response | wct_id |
+-----------+--------------+       +-----------+----------+----------+--------+
|         1 | work order 1 |       |         1 |        1 | ABC      |      1 |
|         2 | work order 2 |       |         1 |        2 | DEF      |      2 |
|         3 | work order 3 |       |         1 |        3 | PMU      |      3 |
|         4 | work order 4 |       |         1 |        4 | XYZ      |      4 |
|         5 | work order 5 |       |         1 |        5 | DEF      |      5 |    <--
+-----------+--------------+       |         2 |        1 | XXX      |      6 |
                                   |         2 |        2 | PMU      |      7 |    <-- ***
                                   |         3 |        1 | ZZZ      |      8 |
                                   |         3 |        2 | YYY      |      9 |
                                   |         3 |        3 | ASE      |     10 |
                                   |         3 |        4 | PMU      |     11 |    <-- ***
                                   |         4 |        1 | PPP      |     12 |
                                   |         4 |        2 | JJJ      |     13 |
                                   |         4 |        3 | PMU      |     14 |
                                   |         4 |        4 | NNN      |     15 |    <--
                                   |         5 |        1 | AAA      |     16 |
                                   |         5 |        2 | BBB      |     17 |
                                   |         5 |        3 | CCC      |     18 |
                                   |         5 |        4 | DDD      |     19 |    <--
                                   +-----------+----------+----------+--------+

    try something like 

    SELECT w.wo_number
     , wo_desc
     , latest.sequence
     , wct.response
FROM work_order w
	 JOIN
	 wct ON w.wo_number = wct.wo_number
            AND wct.response = 'PMU'
	 JOIN 
		 (
			SELECT wo_number
				 , MAX(sequence) as sequence
			FROM wct 
			GROUP BY wo_number
		 ) latest ON wct.wo_number = latest.wo_number
					 AND wct.sequence = latest.sequence
                     
+-----------+--------------+----------+----------+
| wo_number | wo_desc      | sequence | response |
+-----------+--------------+----------+----------+
|         2 | work order 2 |        2 | PMU      |
|         3 | work order 3 |        4 | PMU      |
+-----------+--------------+----------+----------+

     

    Help With Sub/Nested Query

    in MySQL Help

    Posted

    Alternative model which allows multiple siblings 

    jdev_nroll;                                                         jdev_sibling;
+----+--------+---------+-------+-----------+------------+          +------------+----------+
| id | sname  | ctclass | shift | ctstudent | dob        |          | sibling_id | elder_id |
+----+--------+---------+-------+-----------+------------+          +------------+----------+
|  1 | Curly  |       1 |     0 | N         | 2007-01-20 |          |          2 |        1 |
|  2 | Larry  |       1 |     0 | Y         | 2010-12-21 |          |          3 |        1 |
|  3 | Mo     |       1 |     0 | Y         | 2011-02-22 |          |          3 |        2 |
|  4 | Peter  |       1 |     0 | N         | 2009-01-03 |          |          4 |        5 |
|  5 | Paul   |       1 |     0 | N         | 2006-12-21 |          |          9 |        8 |
|  6 | Mary   |       1 |     0 | Y         | 2010-09-20 |          |          9 |       10 |
|  7 | Jane   |       1 |     0 | N         | 2008-03-08 |          |         10 |        8 |
|  8 | John   |       1 |     0 | N         | 2006-10-04 |          +------------+----------+
|  9 | George |       1 |     0 | Y         | 2010-10-26 |
| 10 | Ringo  |       1 |     0 | Y         | 2009-11-15 |
+----+--------+---------+-------+-----------+------------+

SELECT a.id as sibling_id
     , a.sname as sibling_name
     , TIMESTAMPDIFF(YEAR,a.dob,curdate()) as sibling_age
     , a.ctclass as class
     , b.id as elder_id
     , b.sname as elder_name
     , TIMESTAMPDIFF(YEAR,b.dob,curdate()) as elder_age
     , b.ctstudent as elder_ctstudent
FROM jdev_nroll a 
     JOIN
     jdev_sibling s ON a.id = s.sibling_id
     JOIN
     jdev_nroll b ON s.elder_id = b.id
WHERE a.ctstudent = 'Y'    
ORDER BY a.id

+------------+--------------+-------------+-------+----------+------------+-----------+-----------------+
| sibling_id | sibling_name | sibling_age | class | elder_id | elder_name | elder_age | elder_ctstudent |
+------------+--------------+-------------+-------+----------+------------+-----------+-----------------+
|          2 | Larry        |           9 |     1 |        1 | Curly      |        13 | N               |
|          3 | Mo           |           8 |     1 |        1 | Curly      |        13 | N               |
|          3 | Mo           |           8 |     1 |        2 | Larry      |         9 | Y               |
|          9 | George       |           9 |     1 |        8 | John       |        13 | N               |
|          9 | George       |           9 |     1 |       10 | Ringo      |        10 | Y               |
|         10 | Ringo        |          10 |     1 |        8 | John       |        13 | N               |
+------------+--------------+-------------+-------+----------+------------+-----------+-----------------+

     

    • Great Answer 2

    Help With Sub/Nested Query

    in MySQL Help

    Posted · Edited by Barand

    And if a child has a brother and a sister (or two or more of any gender)?

    [edit]

    There's this method, but it doesn't show explicitly that there is also a relationship between Curly and Mo too. 

    SELECT * FROM jdev_nroll;

+----+--------+---------+-------+-----------+---------+-----------+
| id | sname  | ctclass | shift | siblingof | elderof | ctstudent |
+----+--------+---------+-------+-----------+---------+-----------+
|  1 | Curly  |       1 |     0 |         2 |         | Y         |
|  2 | Larry  |       1 |     0 |         3 |       1 | N         |
|  3 | Mo     |       1 |     0 |           |       2 | Y         |
|  4 | Peter  |       1 |     0 |           |       5 | N         |
|  5 | Paul   |       1 |     0 |         4 |         | Y         |
|  6 | Mary   |       1 |     0 |           |         | N         |
|  7 | Jane   |       1 |     0 |           |         | Y         |
|  8 | John   |       1 |     0 |         9 |         | N         |
|  9 | George |       1 |     0 |        10 |       8 | Y         |
| 10 | Ringo  |       1 |     0 |           |       9 | N         |
+----+--------+---------+-------+-----------+---------+-----------+

SELECT a.id
     , a.sname
     , a.ctclass
     , a.shift
     , a.ctstudent
     , b.id as bid
     , b.sname as bname
     , b.ctstudent as bstudent
FROM jdev_nroll a 
     JOIN
     jdev_nroll b ON a.siblingof = b.id;

+----+--------+---------+-------+-----------+----+--------+-----------+
| id | sname  | ctclass | shift | ctstudent |bid | bname  |  bstudent |
+----+--------+---------+-------+-----------+----+--------+-----------+
|  1 | Curly  |       1 |     0 | Y         |  2 | Larry  | N         |
|  2 | Larry  |       1 |     0 | N         |  3 | Mo     | Y         |
|  5 | Paul   |       1 |     0 | Y         |  4 | Peter  | N         |
|  8 | John   |       1 |     0 | N         |  9 | George | Y         |
|  9 | George |       1 |     0 | Y         | 10 | Ringo  | N         |
+----+--------+---------+-------+-----------+----+--------+-----------+

     

    • Great Answer 1

    How to fetch the filename on split

    in PHP Coding Help

    Posted

    9 hours ago, mark107 said:

    I dont store these strings seperate. I stored altogether.

    Then you deserve to have problems :)

    Try 

    $attached_files = <<<DATA
attid: 0 filename: email1.png
attid: 1 filename: email2.png
attid: 2 filename: email3.png
attid: 3 filename: my_inbox.png
DATA;
 
$afArray = explode("\n", "$attached_files");
 
foreach ($afArray as $af) {
    echo trim(strrchr($af, ':'), ': ') . '<br>';
}

     

    How to fetch the filename on split

    in PHP Coding Help

    Posted

    Get the query to do the work. 

    mysql> SELECT attached_files
    ->      , TRIM(SUBSTRING_INDEX(attached_files, ':', -1)) as filename
    -> FROM mark107;
+---------------------------------+--------------+
| attached_files                  | filename     |
+---------------------------------+--------------+
| attid: 0 filename: email1.png   | email1.png   |
| attid: 1 filename: email2.png   | email2.png   |
| attid: 2 filename: email3.png   | email3.png   |
| attid: 3 filename: my_inbox.png | my_inbox.png |
+---------------------------------+--------------+

    How to fetch the filename on split

    in PHP Coding Help

    Posted

    2 minutes ago, mark107 said:

    Why do I need to do that

    Because that is what you asked for ...

     

    47 minutes ago, mark107 said:

    Here is what I have stored in the database: 

    
 attid: 0 filename: email1.png
 attid: 1 filename: email2.png
 attid: 2 filename: email3.png
 attid: 3 filename: my_inbox.png


     

     

    Here is what I want to achieve: 

    
email1.png
email2.png
email3.png
my_inbox.png

     

     

    Can you please show me example how I could get the filename when I am fetching the data from the database?

     

    Use insert ids from an array in another query

    in PHP Coding Help

    Posted

    I'm guessing the above code is to be used to process the form in your other post where you are adding new (cloned) items.

    You data model ... 

    +---------------+                   +---------------+
|      Menu     |                   |      Item     |
+---------------+                   +---------------+
       |                                    |
       |          +-----------------+       | 
       +---------<| menu_connection |>------+
                  +-----------------+

    ... implies an item can appear on many menus, yet your form does not allow for the allocation of aleady existing items to a menu, just newly created items.

    Just curious.

    Use insert ids from an array in another query

    in PHP Coding Help

    Posted

    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();
    }

     

    • Great Answer 1

    Don’t know how to get values from stored procedure

    in PHP Coding Help

    Posted

    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;   
}

     

    search into tables

    in PHP Coding Help

    Posted · Edited by 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)

    search into tables

    in PHP Coding Help

    Posted

    16 minutes ago, Revolutsio said:

    The code did work

    because you needed a PDO connection

     

    16 minutes ago, Revolutsio said:

    my question was how do I put the results in a table

    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>

     

    Don’t know how to get values from stored procedure

    in PHP Coding Help

    Posted · Edited by Barand

    On 1/12/2020 at 9:21 PM, Barand said:

    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.

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

     

×
×
  • 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.