CUTCODEDOWN
Minimalist Semantic Markup

Welcome Guest
Please Login or Register

If you have registered but not recieved your activation e-mail in a reasonable amount of time, please use our Contact Form for assistance. Include both your username and the e-mail you tried to register with.

Author Topic: WordPress Database Driver and using mySQL NOW()  (Read 1346 times)

GrumpyYoungMan

  • Full Member
  • ***
  • Posts: 217
  • Karma: +1/-0
    • GrumpyYoungMan
WordPress Database Driver and using mySQL NOW()
« on: 14 Jan 2020, 07:01:40 am »
I know the thoughts around here about WordPress, but please bear with me - I have my reason for porting this project to WP.

How can I use the mySQL command via the WordPress mySQL driver using $wpdb->insert();

Code: [Select]
$wpdb->insert("wp_mots",

[

'mot_vrm' => $vrm,
'mot_expiry' => "${_POST['expiry']}",
'mot_uid' => get_current_user_id(),
'mot_added' => "NOW()",]
);
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Coder - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Sr. Member
  • *****
  • Posts: 405
  • Karma: +62/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: WordPress Database Driver and using mySQL NOW()
« Reply #1 on: 14 Jan 2020, 09:58:09 am »
Methinks your problem is a mix of typo's and unneccessary "{}"

Since "" parses of $, and {} is for wrapping {} that contains quotes, this:

'mot_expiry' => "${_POST['expiry']}",

Was probably supposed to be this:

'mot_expiry' => "{$_POST['expiry']}",

Which is in all likelihood functionally identical to this:

'mot_expiry' => $_POST['expiry'],

... one of the many reasons I consider double quoted strings and the BS inlined parsing of them to be incompetent trash. Along with heredoc and nowdoc they're amongst the many things I'd have stricken from the language.

That said I'm not sure the mysql command NOW() can actually even be added as a value any more than it can from a normal prepared query. As it's an actual SQL command you can't (or at least shouldn't) be able to do that any more than you could from PDO or MYSQLi using prepare. It should be intentionally escaping that so as to avoid injections.
« Last Edit: 14 Jan 2020, 10:00:58 am by Jason Knight »
Sorrow hides well in your shell. A fellow man with hurt to spare.
Dear one, here I am to share the fear. An act of kindness, without an amen.
Come in, the fire's warm. Burn the rope and dance some more.

GrumpyYoungMan

  • Full Member
  • ***
  • Posts: 217
  • Karma: +1/-0
    • GrumpyYoungMan
Re: WordPress Database Driver and using mySQL NOW()
« Reply #2 on: 14 Jan 2020, 01:28:46 pm »
You are absolutely correct - I did make several typos!

So I will be best to create a date and time stamp via PHP rather than rely on the MySQL NOW() function?

I’m sure in my stand alone project I found a way to use NOW()...

Thanks again for the feedback... I’ll post some more code up once I have the laptop fired up!
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Coder - not sure that is the right thing to say... but trying to learn...

GrumpyYoungMan

  • Full Member
  • ***
  • Posts: 217
  • Karma: +1/-0
    • GrumpyYoungMan
Re: WordPress Database Driver and using mySQL NOW()
« Reply #3 on: 14 Jan 2020, 02:08:31 pm »
In my stand alone project I am doing something like this:

Code: [Select]
// INSERT Vehicle into database:
    $sql = array ( 'query' => "INSERT INTO vehicles (`vehicle_vrm`, `vehicle_added`) VALUES(:vrm, NOW())",
                    'data' => array( ':vrm' => "$vrm") );

    $DB->db_PrepareExecuteQuery($sql);

db_driver:
Code: [Select]
function db_PrepareExecuteQuery($sql) {
       
        $this->queries[] = "{$sql['query']}";
       
        $this->stmt = $this->PDO->prepare($sql['query']);
        $this->stmt->execute($sql['data']);
               
    }

I am guessing I shouldn't be?
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Coder - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Sr. Member
  • *****
  • Posts: 405
  • Karma: +62/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: WordPress Database Driver and using mySQL NOW()
« Reply #4 on: 14 Jan 2020, 03:54:56 pm »
I can't say as I gave up on all that pointless wrapper, query building BS back when PDO became viable over a decade ago.

Get the ACTUAL DB connect, and if it's PDO:

Code: [Select]
$stmt = $db->prepare('
INSERT INTO vehicles (
vehicle_vrm, vehicle_added
) VALUES(
?, NOW()
)'
);
$stmt->execute([$vrm]);

... and be done with it. These dipshit dumbass "wrappers" for SQL are relicts meant to get around the limitations of the old -- now nonexistent --  mysql_ functions, and really have no place in any legitimate code written after 2004.

But much like mysql_ itself, or HTML 3.2 style practices, people keep doing it...

The truly mind-numbing derp of that BS being that they tend to CREATE more security holes than they 'fix'.

I mean seriously, LOOK at this:

Code: [Select]
$this->queries[] = "{$sql['query']}";

LOOK AT IT!!! Good gravy man...
« Last Edit: 14 Jan 2020, 03:57:46 pm by Jason Knight »
Sorrow hides well in your shell. A fellow man with hurt to spare.
Dear one, here I am to share the fear. An act of kindness, without an amen.
Come in, the fire's warm. Burn the rope and dance some more.

GrumpyYoungMan

  • Full Member
  • ***
  • Posts: 217
  • Karma: +1/-0
    • GrumpyYoungMan
Re: WordPress Database Driver and using mySQL NOW()
« Reply #5 on: 14 Jan 2020, 05:36:55 pm »
Thanks Jason - The query was only add to a big "super" query array while I was testing the script - it would have been removed before it was ever seen in pubic - Honestly!

I put the PDO in an "wrapper" encase I ever changed drivers in the future? but is this a waste of time?

I will take what you say on board and change the coding accordingly!
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Coder - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Sr. Member
  • *****
  • Posts: 405
  • Karma: +62/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: WordPress Database Driver and using mySQL NOW()
« Reply #6 on: 14 Jan 2020, 06:33:45 pm »
I put the PDO in an "wrapper" encase I ever changed drivers in the future? but is this a waste of time?
Well, in the latter case where you have the query written out? Yes. Because what needs to change isn't the wrapper or much of anything the wrapper could do, it's the QUERY due to minor syntax changes.

It's why I prefer to "secure" my queries via what's called "named queries". All queries are pre-built in their own files PER SUPPORTED DRIVER.

For example if we had driver support for that insert, we'd have something like:

/pdo/mysql/vehicle_insert_vrm.sql
/pdo/postgre/vehicle_insert_vrm.sql
/pdo/sqlite/vehicle_insert_vrm.sql

Due to there POSSIBLY being minor differences in the calling code (such as polyfilling in MSSQL's re-re lack of results return) the extension to PDO would have it's own copy in each of those directories as well where/as needed, falling back to one in /pdo if the /pdo/driver/ one doesn't exist.

Then you overload the existing query, exec, and prepare methods to ONLY accept the NAME of the queries -- such as:

$db->prepare('vehicle_insert_vrm')

To prevent queries built in PHP from being loaded. You need a different query, create the file(s) for it. Makes it a hair harder for code elevations to abuse the database. More so if you split them up into subdirectories that match user permission levels and the calling routines.

Then of course you chown them to root / su, and set them to 644 so good luck PHP -- or much of anything else -- writing to them, making them inviolate.

When you run your overloaded constructor, extract the database type from the DSN, and use it to access the correct directory of queries for the appropriate driver. Want to add support for a new driver? Make a directory that matches the DSN label, copy over and convert all your queries, change the PDO extend as appropriate, done.

Apart from the fact you would be passing the name of the query instead of an actual query, PDO's behavior itself would remain largely unchanged. You'd still just 'bindParam' and/or execute normally.

Named queries are an old OLD technique that for some reason disappeared sometime around when SQL started to gain actual market traction, but it's a perfect match for how PDO can support multiple DB drivers.
Sorrow hides well in your shell. A fellow man with hurt to spare.
Dear one, here I am to share the fear. An act of kindness, without an amen.
Come in, the fire's warm. Burn the rope and dance some more.

GrumpyYoungMan

  • Full Member
  • ***
  • Posts: 217
  • Karma: +1/-0
    • GrumpyYoungMan
Re: WordPress Database Driver and using mySQL NOW()
« Reply #7 on: 15 Jan 2020, 02:27:40 am »
Thanks, I will look at making the changes!

I appreciate your feedback, I am sorry for such the novice and silly posts...
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Coder - not sure that is the right thing to say... but trying to learn...

GrumpyYoungMan

  • Full Member
  • ***
  • Posts: 217
  • Karma: +1/-0
    • GrumpyYoungMan
Re: WordPress Database Driver and using mySQL NOW()
« Reply #8 on: 17 Jan 2020, 10:56:43 am »
I guess ultimately this is a problem with using a "framework" rather than your own code  - which I am much happier doing, this WP project was only meant to be a quick and dirty side project!

Anyhow, I have got the mySQL NOW() command to work, but the method seems very long-winded...

Code: [Select]
$wpdb->query($wpdb->prepare("INSERT INTO {$wpdb->prefix}mots ( `mot_vrm`, `mot_expiry`, `mot_uid`, `mot_added` ) VALUES ( %s, %s, %s, NOW() )",

array( "ab12abc", date("Y-m-d"), "1" )

));

It would have been nice if I could use the named placeholders eg: :vrm, :expiry, :uid.. and not be forced to used the %s...
« Last Edit: 17 Jan 2020, 11:31:46 am by GrumpyYoungMan »
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Coder - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Sr. Member
  • *****
  • Posts: 405
  • Karma: +62/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: WordPress Database Driver and using mySQL NOW()
« Reply #9 on: 17 Jan 2020, 02:43:00 pm »
I guess ultimately this is a problem with using a "framework" rather than your own code  - which I am much happier doing, this WP project was only meant to be a quick and dirty side project!
It's what I've found every single blasted time with every single off the shelf CMS or framework. It's more work turning "quick and dirty projects" into a multi-week disasters. They don't save me any time, they don't do what I want done, and they don't work how I work. They just add overhead and lies.

The handful of things people THINK they speed up are usually so simple that if you need a framework for it, you shouldn't be writing PHP (or JavaScript, or whatever) in the first damned place yet... and the moment you tread outside what they were designed for "out of box" they make it painful, convoluted, and ridiculously difficult for no good reason taking many, MANY times more effort than it saves anywhere else.

Side note, this is 2020, drop the array() nonsense for [] already. :D
Sorrow hides well in your shell. A fellow man with hurt to spare.
Dear one, here I am to share the fear. An act of kindness, without an amen.
Come in, the fire's warm. Burn the rope and dance some more.

GrumpyYoungMan

  • Full Member
  • ***
  • Posts: 217
  • Karma: +1/-0
    • GrumpyYoungMan
Re: WordPress Database Driver and using mySQL NOW()
« Reply #10 on: 19 Jan 2020, 03:42:17 am »
I guess ultimately this is a problem with using a "framework" rather than your own code  - which I am much happier doing, this WP project was only meant to be a quick and dirty side project!
It's what I've found every single blasted time with every single off the shelf CMS or framework. It's more work turning "quick and dirty projects" into a multi-week disasters. They don't save me any time, they don't do what I want done, and they don't work how I work. They just add overhead and lies.

The handful of things people THINK they speed up are usually so simple that if you need a framework for it, you shouldn't be writing PHP (or JavaScript, or whatever) in the first damned place yet... and the moment you tread outside what they were designed for "out of box" they make it painful, convoluted, and ridiculously difficult for no good reason taking many, MANY times more effort than it saves anywhere else.
I hate frameworks too, but it kind off made sense to use it to get interest in this project - May be easier to do that than a whole stand alone project?

Side note, this is 2020, drop the array() nonsense for [] already. :D
I already have but missed that one! Sorry! :) ;)

Thanks again for taking the time to reply and assist me, I don’t want to spam you and your forums too much...
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Coder - not sure that is the right thing to say... but trying to learn...

GrumpyYoungMan

  • Full Member
  • ***
  • Posts: 217
  • Karma: +1/-0
    • GrumpyYoungMan
Re: WordPress Database Driver and using mySQL NOW()
« Reply #11 on: 23 Jan 2020, 10:34:58 am »
As you will probably now I have been playing around with a dirty script to insert and create false reminder records.

The problem I am now coming across is that the only method Wordpress seems to allow is the get_results() which gets every result found in an array (clue in the name really!?) - what I would like to know is is there a way of building up the information via a while loom? as my demo script has created 10k+ records and you know what that is doing to my poor i7 lappy....

Should I just create and use my own PDO class?
« Last Edit: 24 Jan 2020, 03:33:08 am by GrumpyYoungMan »
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Coder - not sure that is the right thing to say... but trying to learn...

 

Advertisement