Insert the value of a <txp:variable/> into a MySQL database

geoff777 · 2009-03-16 18:13:22

I need to add a couple of <txp:variable/> into a MySQL database.

I guess I need the <txp:variable/> to become a php variable.

The PHP I need is this.

$sql = ‘INSERT INTO `PageCount`.`countertable` (`date`, `section`, `story`) VALUES (CURDATE, \‘Section\’, \‘2\’);’;

I hope something like this will work.

$sql = ‘INSERT INTO `PageCount`.`countertable` (`date`, `section`, `story`) VALUES (CURDATE, \’$Section\’, \’$number\’);’;

How do I convert the <txp:variable name=“number”/> to become a php variable $number?

I was wondering if smd_query might work, I’ve never used it.

I’m not really too familiar with PHP and MySQL is there an easy way to total yesterday’s count for a particular section?

Thanks Geoff

Last edited by geoff777 (2009-03-16 18:14:27)

Bloke · 2009-03-16 18:19:03

geoff777 wrote:

I guess I need the <txp:variable/> to become a php variable.

Try $variable['name_of_the_variable']

geoff777 · 2009-03-16 19:18:29

Thanks Stef,

I’ve got …

$sql = ‘INSERT INTO `PageCount`.`countertable` (`date`, `section`, `story`) VALUES (CURDATE, \’$variable[‘section_name’]\’, \’$variable[“story_number”]\’);’;

Not sure I’ve got the variables right?
I’m never sure of apostrophes in MySQL.

I’m fairly sure I’m connecting to the db but this query isn’t working.

Thanks
Geoff

Bloke · 2009-03-16 19:42:07

geoff777 wrote:

I’m never sure of apostrophes in MySQL.

Yeah, it’s a bit of a mare. Let’s see. I’m not 100% sure on this but I’d bet the apostrophes are causing hassle here. Try:

$sql = "INSERT INTO `PageCount`.`countertable` (`date`, `section`, `story`) VALUES (CURDATE(), '" .$variable['section_name']. "', '" .$variable['story_number']. "');";

A few things might have been scuppering it:

The apostrophes around section_name would cause the end of the $sql assignment and you’d get an error about everything afterwards
The fact that inside apostrophes in PHP, $ vars are not expanded (confusingly, the opposite to the way the TXP parser works!), so your SQL statement probably contained a literal $variable['section_name']
Sometimes expanding array variables inside a string is hit and miss. More miss than hit I’ve found so I tend to shy away from it

In the above, using double quotes around the whole string just allows you to get away without escaping the apostrophes. I’d steer clear of trying to expand arrays inside a string, so I chose to concatenate them with the dot operator outside the string itself.

Give that a whirl and see if it gets you any further.

geoff777 · 2009-03-17 10:35:19

Hi Stef

Thanks for taking the time to explain the apostophes. I like the concatenating method.

I’ve been playing around with the code. I’ve got the CURDATE to work on a test table.
But even on a two column table I can’t get the variable to work.

“ .$variable[‘section_name’]. “ just doesn’t work for me.

As a test I tried to see if I could ‘echo’ any <txp:variable/> using

<txp:php> echo $variable[‘var_name’]; </txp:php>

It didn’t work.
Could you check and see if you can get an echo …

Thanks Geoff

Bloke · 2009-03-17 10:41:20

geoff777 wrote:

As a test I tried to see if I could ‘echo’ any <txp:variable/>

Doh! Sorry, forgot to say you need to import $variable from the global scope. Try adding global $variable; first ;-)

geoff777 · 2009-03-17 10:52:21

Brilliant Stef,

Worked first time.
Now I have the date and section stored for every page view.
I’d like to get totals for yesterday, the day before yesterday … lol

Thanks Geoff

Bloke · 2009-03-17 11:04:31

geoff777 wrote:

I’d like to get totals for yesterday, the day before yesterday … lol

I’m not the world’s greates MySQLer but I think something like this might do it:

SELECT section, COUNT(*) FROM `PageCount`.`countertable` WHERE date BETWEEN '2009-03-15 00:00:00' AND '2009-03-15 23:59:59' GROUP BY section

That should (untested, may be liable to subsidence) get the section and the total number of stories in each, between the specified dates. There’s probably a better way from those who are more in-the-know.

If you want to make it more generic (like to always get the values ‘yesterday’) you’ll have to muck about with generating the dates instead of hard-coding them. I think MySQL has some good date handling functions/shortcuts or you could always fall back to PHP to get the dates. smd_query will be able to handle it no sweat. Hope that helps.

Last edited by Bloke (2009-03-17 11:06:48)

geoff777 · 2009-03-19 08:00:08

Thanks Stef,

I’ll leave it for a few days to allow the data to build up.

Geoff

Textpattern CMS

Textpattern CMS support forum

#1 2009-03-16 18:13:22

Insert the value of a <txp:variable/> into a MySQL database

#2 2009-03-16 18:19:03

Re: Insert the value of a <txp:variable/> into a MySQL database

#3 2009-03-16 19:18:29

Re: Insert the value of a <txp:variable/> into a MySQL database

#4 2009-03-16 19:42:07

Re: Insert the value of a <txp:variable/> into a MySQL database

#5 2009-03-17 10:35:19

Re: Insert the value of a <txp:variable/> into a MySQL database

#6 2009-03-17 10:41:20

Re: Insert the value of a <txp:variable/> into a MySQL database

#7 2009-03-17 10:52:21

Re: Insert the value of a <txp:variable/> into a MySQL database

#8 2009-03-17 11:04:31

Re: Insert the value of a <txp:variable/> into a MySQL database

#9 2009-03-19 08:00:08

Re: Insert the value of a <txp:variable/> into a MySQL database

Board footer