Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,425

  • Joined

  • Last visited

  • Days Won

    806

 Content Type 

Posts posted by Barand

    Outputting MySql data that contains ' into php variables

    in PHP Coding Help

    Posted

    Strange!. Line 132 doesn't give a problem for me. However, I would write it differently, removing the concatenation by using {..}s around the array variables 

    echo "<option value='{$row['role_id']}'>{$row['role_name']}</option>";

    Alternatively, as constants are not permitted within a string, the single quotes around the keys can be omitted 

    echo "<option value='$row[role_id]'>$row[role_name]</option>";

     

    42 minutes ago, Adamhumbug said:

    I am in the process of changing all my php to use prepared stmts but have not got to this one yet

    That one doesn't need preparing - query() is fine. No parameters.

    Outputting MySql data that contains ' into php variables

    in PHP Coding Help

    Posted · Edited by Barand

    One of things I really liked about PHP, having previously used VB (ugh!) was the ability to directly embed variables into strings without all the confusing quoting and concatenating. Coupled with the ability to use HEREDOC syntax life became much easier when confronted with strings like this one.

    I would suggest... 

    echo <<<TEXT
<td><a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="$uid" href="#userModal" data-firstname="$ufn" data-lastname="$uln" data-email="$ue" data-accountlevel="$ualid" data-mobile="$um" data-role="$urid" data-active-sheets="$ename">Manage</a></td>
TEXT;

    Alternatively, go for a string inside "...." and with single quotes around attribute values, BUT, where you have an attribute value that could contain a single quote character or apostrophe, use escaped double quotes. EG 

    echo "<td><a class='btn btn-primary col-sm-12' data-toggle='modal' data-userid='$uid' href='#userModal' data-firstname=\"$ufn\" data-lastname=\"$uln\" data-email='$ue' data-accountlevel='$ualid' data-mobile='$um' data-role='$urid' data-active-sheets=\"$ename\">Manage</a></td>";

     

    • Great Answer 1

    Selecting array elements based on an index range

    in PHP Coding Help

    Posted

    One way ... 

    $list=[
        12314=>'OBJ1',
        321=>'OBJ2',
        42142=>'OBJ3',
        14314=>'OBJ4',
        123=>'OBJ5',
        13314=>'OBJ6'
    ];

function getIt($list, $min, $max)
{
    $arr = array_filter($list, function ($k) use ($min,$max) {
                                   return $k >= $min && $k <= $max;
                                }, ARRAY_FILTER_USE_KEY);
    krsort($arr);
    return current($arr);
}

echo getIt($list, 10000, 15000);          // OBJ4
echo getIt($list, 15000, 20000);          // false

     

    Use a qry result in a new qry

    in MySQL Help

    Posted

    Join twice to the user table with different aliases 

    select a.job_manager_id
     , jm.username as manager
     , a.job_head_chef_id
     , hc.username as chef
FROM ssm.job a
     JOIN user jm ON jm.user_id = a.job_manager_id
     JOIN user hc ON hc.user_id = a.head_chef_id

     

    • Great Answer 1

    Delete from multiple tables at once

    in PHP Coding Help

    Posted

    For example, if you have these tables ... 

    CREATE TABLE `test1` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  `test1_descrip` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
	CREATE TABLE `test2` (
  `test2_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test2_id`),
  KEY `idx_test2_job_id` (`job_id`),
  CONSTRAINT `test2_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
	CREATE TABLE `test3` (
  `test3_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test3_id`),
  KEY `idx_test3_job_id` (`job_id`),
  CONSTRAINT `test3_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	 
CREATE TABLE `test4` (
  `test4_id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`test4_id`),
  KEY `idx_test4_job_id` (`job_id`),
  CONSTRAINT `test4_fk1` 
        FOREIGN KEY (`job_id`) 
        REFERENCES `test1` (`job_id`) 
        ON DELETE CASCADE 
        ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ... where test1 is the parent table and tables 2. 3 and 4 are the child tables, each with a foreign key referencing the primary key in the first table
     

    BEFORE
                            +--------+---------------+
                            | job_id | test1_descrip |
                            +--------+---------------+
                            |     20 | Job 20        |
                            |     21 | Job 21        |
                            |     22 | Job 22        |
                            |     23 | Job 23        |
                            |     24 | Job 24        |
                            |     25 | Job 25        |
                            +--------+---------------+
                                  |
               +------------------+----------+------------------------------+                 
               |                             |                              |
+----------+--------+         +----------+--------+          +----------+--------+
| test2_id | job_id |         | test3_id | job_id |          | test4_id | job_id |
+----------+--------+         +----------+--------+          +----------+--------+
|        8 |     21 |         |        2 |     20 |          |        4 |     21 |
|        7 |     24 |         |        3 |     21 |          |        5 |     22 |
+----------+--------+         |        4 |     22 |          |        6 |     23 |
                              +----------+--------+          |        7 |     24 |
                                                             +----------+--------+

    Delete query...

    DELETE FROM test1 WHERE job_id = 21;

    Records for job 21 also deleted from test2, test3, test4 due to the "ON DELETE CASCADE" settings in the foreign key definitions

    AFTER
                            +--------+---------------+
                            | job_id | test1_descrip |
                            +--------+---------------+
                            |     20 | Job 20        |
                            |     22 | Job 22        |
                            |     23 | Job 23        |
                            |     24 | Job 24        |
                            |     25 | Job 25        |
                            +--------+---------------+
                                  |
               +------------------+----------+------------------------------+                 
               |                             |                              |
+----------+--------+       +----------+--------+             +----------+--------+
| test2_id | job_id |       | test3_id | job_id |             | test4_id | job_id |
+----------+--------+       +----------+--------+             +----------+--------+
|        7 |     24 |       |        2 |     20 |             |        5 |     22 |
+----------+--------+       |        4 |     22 |             |        6 |     23 |
                            +----------+--------+             |        7 |     24 |
                                                              +----------+--------+


     

    Delete from multiple tables at once

    in PHP Coding Help

    Posted · Edited by Barand

    10 minutes ago, ginerjm said:

    wouldn't all of the tables have to have a column name of job_id

     

    That was established in the question

    2 hours ago, Adamhumbug said:

    There is going to be 10 or so tables that need to delete *  where $job_id = ?

     

    10 minutes ago, ginerjm said:

    And - is "FROM" a required field to distinguish column names from table names

    ???

    FROM is part of the DELETE syntax just as it is part of the SELECT syntax and has the same role - defining the table references.

    Delete from multiple tables at once

    in PHP Coding Help

    Posted

    As with SELECT statements, INNER JOIN requires matching records.

    If some tables may not contain the job_id being deleted then you need LEFT JOINS 

    DELETE ssm_job, ssm_menu_order, ssm_equipment_order, ssm_money_order 
FROM ssm_job
   LEFT JOIN ssm_menu_order USING (job_id)
   LEFT JOIN ssm_equipment_order USING (job_id)
   LEFT JOIN ssm_money_order USING (job_id)
WHERE ssm_job.job_id = ?

     

    • Great Answer 1

    Creating executable sql code from html tables

    in PHP Coding Help

    Posted · Edited by Barand

    Something along these lines... 

    include("path/to/simple_html_dom.php");

$html = file_get_html('mysource.html');

$rows = $html->find('tr[bgcolor="#EEEEEE"]');

$stmt = $conn->prepare("INSERT INTO thetable (uid, ... , etc)" VALUES(?,?,?, ...., etc));

foreach ($rows as $row) {
    if (strpos($row->children(0), '<th>')!==false) continue;
    $data = [];
    foreach ($row->children() as $k => $itm) {
        $val = $itm->plaintext;
        switch ($k) {
            case 6:                                 // DoB
            case 9:                                 // Expires
                $data[] = reformatDate($val);
                break;
            case 7:                                 // Height
            case 8:                                 // Weight
            case 10:                                // Wage
                $data[] = numOnly($val);
                break;
            case 11:                                // Value
                $data[] = reformatValue($val); 
                break;    
            default: 
                $data[] = $val;
        }
    }
    $stmt->execute($data);
    
}

function reformatDate($str)
{
    list($dob) = explode(' ', $str);
    $res = (DateTime::createFromFormat('d/m/Y', $dob))->format('Y-m-d') ;
    return $res;
}

function numOnly($str)
{
    $res = '';
    for($i=0, $k=strlen($str); $i<$k; $i++) {
        $res .= ctype_digit($str[$i]) ? $str[$i] : '';
    }
    return $res;
}

function reformatValue($str)
{
    //
    // This is left as an exercise for the reader!
    //
    return $str;      // leave unchanged for now
}

     

    Creating executable sql code from html tables

    in PHP Coding Help

    Posted · Edited by Barand

    26 minutes ago, seany1234 said:

    how would it be possible to format data

    You would do the required formatting before writing to the csv file.

     

    26 minutes ago, seany1234 said:

    remove the first row?

    LOAD DATA INFILE command has a "IGNORE N rows" option for ignoring headings.

    If you don't want to use that LOAD DATA option (which is certainly the fastest and will load hundreds of records per second) then , with a PDO connection and a prepared statement you could change the loop to 

    $stmt = $conn->prepare("INSERT INTO thetable (uid, ... , etc)" VALUES(?,?,?, ...., etc));

foreach ($rows as $row) {
    $data = [];
    foreach ($row->children() as $itm) {
        $val = $itm->plaintext;
// do any required reformatting here
        $data[] = $val;
    }
    $stmt->execute($data);
}

     

    Inserting time stamp broke my database:

    in MySQL Help

    Posted

    A time stamp (eg 2019-12-15 19:02:00) is a string value and should therefore be inside single quotes in your query.

    (This would be the same in C# as it is purely SQL related, not C# or PHP).

    You should (in either language) be using prepared queries and not embedding variables in the query. For example (using PDO) 

    $stmt = $conn->prepare("UPDATE userData SET timestamp = ? WHERE GUID = ?")
$stmt->execute([ $timeStamp, $LoggedIn_guid ]);

     

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