Language: 
To browser these website, it's necessary to store cookies on your computer.
The cookies contain no personal information, they are required for program control.
  the storage of cookies while browsing this website, on Login and Register.

User

Welcome, Guest. Please login or register.
Did you miss your activation email?


Login with username, password and session length

Select language:

Community



Stats

  • *Total Members: 4442
  • *Latest: Argon2

  • *Total Posts: 16406
  • *Total Topics: 2506
  • *Online Today: 14
  • *Most Online: 292
(12.11.16, 09:37:31)
  • *Users: 0
  • *Guests: 0
  • *Spiders: 4
  • *Total: 4

  • *Google
  • *Baidu (3)

Author Topic: Sql - db_query question  (Read 9857 times)

0 Members and 0 Guests are viewing this topic.

Offline Cool

  • Free Tibet
  • PortaMx Supporter
  • *
  • Posts: 744
  • Gender: Male
  • I Hate Chinas Authority
Sql - db_query question
« on: 25.09.08, 15:25:05 »
hi,
how to fix the old-style query:
Code: [Select]
$request = db_query("
SELECT

to work with portamx php-blocks
|I Use: SMF 2.0.5 | PortaMx 1.51
Q: What do you do when you see a bear in the woods?
A: Play Dead!

H-A-H-L = Happy Admins Have Linux

Offline feline

  • CO PortaMx corp.
  • Administrator
  • *
  • Posts: 5786
  • Gender: Female
Re: Sql - db_query question
« Reply #1 on: 25.09.08, 15:49:39 »
Uuuuhhh  8)
The database query in SMF 2.0 are completly changed ..
For all queries you have to use the internal db functions.

Example:
Code: [Select]
// The function array that hold the DB function
Global $smcFunc;

// The Query
$request = $smcFunc['db_query']('', '
SELECT colname1, colname2, colname3
FROM {db_prefix}tablename
WHERE colname1 = {int:int_value}',
array(
'int_value' => 1,
)
);

// Fetch values
while($row = $smcFunc['db_fetch_assoc']($request))
{
$var = $row['colname1'];
...
}

// Free the resurce
$smcFunc['db_free_result']($request);

For more information on the new structure please read this thread:
http://www.simplemachines.org/community/index.php?topic=216143.msg1377936#msg1377936

Fel
Many are stubborn in relation to the path, a few in relation to the target.

Offline Cool

  • Free Tibet
  • PortaMx Supporter
  • *
  • Posts: 744
  • Gender: Male
  • I Hate Chinas Authority
Re: Sql - db_query question
« Reply #2 on: 25.09.08, 16:25:23 »
please could you fix this one if you find the time or in the mood for it.
no hurry!

Code: [Select]
global $db_prefix,$modSettings,$smcFunc;

$today = strtotime("24 hours ago");
date('j') == 1 ? $thismonth = $today : $thismonth = strtotime(date('F') . ' 1');
date('l') == 'Sunday' ? $thisweek = $today : $thisweek = strtotime('last Sunday');
date('M') == 'January' ? $thisyear = $thismonth : $thisyear = strtotime('January 1');

$request = $smcFunc['db_query']('', '
SELECT
COUNT(dateRegistered > $thisyear OR NULL) as year,
COUNT(dateRegistered > $thismonth OR NULL) as month,
COUNT(dateRegistered > $thisweek OR NULL) as week,
COUNT(dateRegistered > $today OR NULL) as today
FROM {$db_prefix}members
WHERE is_activated = 1", __FILE__, __LINE__);

//$row = mysql_fetch_assoc($query);

settype($row['today'], "integer");
settype($row['week'], "integer");
settype($row['month'], "integer");
while($row = $smcFunc['db_fetch_assoc']($request))
{
echo 'Total Members: ', $modSettings['totalMembers'],'<br />';
echo 'New This Month: ', $row['month'],'<br />';
echo 'New This Week: ', $row['week'],'<br />';
echo 'New Today: ', $row['today'];
}
// Free the resurce
$smcFunc['db_free_result']($request);

thank you
|I Use: SMF 2.0.5 | PortaMx 1.51
Q: What do you do when you see a bear in the woods?
A: Play Dead!

H-A-H-L = Happy Admins Have Linux

Offline feline

  • CO PortaMx corp.
  • Administrator
  • *
  • Posts: 5786
  • Gender: Female
Re: Sql - db_query question
« Reply #3 on: 26.09.08, 16:20:30 »
Try this ...  ;)

Code: [Select]
global $modSettings,$smcFunc;

$today = strtotime("24 hours ago");
date('j') == 1 ? $thismonth = $today : $thismonth = strtotime(date('F') . ' 1');
date('l') == 'Sunday' ? $thisweek = $today : $thisweek = strtotime('last Sunday');
date('M') == 'January' ? $thisyear = $thismonth : $thisyear = strtotime('January 1');

$request = $smcFunc['db_query']('', '
SELECT
COUNT(dateRegistered > $thisyear OR NULL) as year,
COUNT(dateRegistered > $thismonth OR NULL) as month,
COUNT(dateRegistered > $thisweek OR NULL) as week,
COUNT(dateRegistered > $today OR NULL) as today
FROM {$db_prefix}members
WHERE is_activated = {int:activated}',
array(
'activated' => 1,
),
);

settype($row['today'], "integer");
settype($row['week'], "integer");
settype($row['month'], "integer");

while($row = $smcFunc['db_fetch_assoc']($request))
{
echo 'Total Members: ', $modSettings['totalMembers'],'<br />';
echo 'New This Month: ', $row['month'],'<br />';
echo 'New This Week: ', $row['week'],'<br />';
echo 'New Today: ', $row['today'];
}
// Free the resurce
$smcFunc['db_free_result']($request);

Fel
Many are stubborn in relation to the path, a few in relation to the target.

Offline Cool

  • Free Tibet
  • PortaMx Supporter
  • *
  • Posts: 744
  • Gender: Male
  • I Hate Chinas Authority
Re: Sql - db_query question
« Reply #4 on: 26.09.08, 17:10:09 »
thank you very very much,
but unfortunately it will not work.
it give:
Code: [Select]
Parse error: syntax error, unexpected ')' in /nfs/c01/h02/mnt/1234/domains/domain.tld/html/smf2/Sources/PortaMx/Class/php.php(23) : eval()'d code on line 19
if i try to fix that i get other errors(do to lack sql-knowledge i think)

|I Use: SMF 2.0.5 | PortaMx 1.51
Q: What do you do when you see a bear in the woods?
A: Play Dead!

H-A-H-L = Happy Admins Have Linux

Offline feline

  • CO PortaMx corp.
  • Administrator
  • *
  • Posts: 5786
  • Gender: Female
Re: Sql - db_query question
« Reply #5 on: 26.09.08, 22:40:12 »
Ooops ... sorry, I have not seen the $var in query .. corrected:
Code: [Select]
$request = $smcFunc['db_query']('', '
SELECT
COUNT(dateRegistered > {string:thisyear} OR NULL) as year,
COUNT(dateRegistered > {string:thismonth} OR NULL) as month,
COUNT(dateRegistered > {string:thisweek} OR NULL) as week,
COUNT(dateRegistered > [sting:today} OR NULL) as today
FROM {$db_prefix}members
WHERE is_activated = {int:activated}',
array(
'activated' => 1,
'thisyear' => $thisyear,
'thismonth' => $thismonth,
'thisweek' => $thisweek,
'today' => $today,
),
);

Fel
Many are stubborn in relation to the path, a few in relation to the target.

Offline Cool

  • Free Tibet
  • PortaMx Supporter
  • *
  • Posts: 744
  • Gender: Male
  • I Hate Chinas Authority
Re: Sql - db_query question
« Reply #6 on: 27.09.08, 09:55:31 »
same error as before
|I Use: SMF 2.0.5 | PortaMx 1.51
Q: What do you do when you see a bear in the woods?
A: Play Dead!

H-A-H-L = Happy Admins Have Linux

Offline feline

  • CO PortaMx corp.
  • Administrator
  • *
  • Posts: 5786
  • Gender: Female
Re: Sql - db_query question
« Reply #7 on: 27.09.08, 12:20:05 »
replace
Code: [Select]
   FROM {$db_prefix}memberswith
Code: [Select]
   FROM {db_prefix}members
Fel
Many are stubborn in relation to the path, a few in relation to the target.

Offline Cool

  • Free Tibet
  • PortaMx Supporter
  • *
  • Posts: 744
  • Gender: Male
  • I Hate Chinas Authority
Re: Sql - db_query question
« Reply #8 on: 27.09.08, 14:43:49 »
not better
allways get a:
Code: [Select]
Parse error: syntax error, unexpected ')' in
/nfs/c01/h02/mnt/123/domains/tdomain.tld/html/smf2/Sources/PortaMx/Class/php.php(23) : eval()'d code on line 23

last complete code is:
Code: [Select]
global $modSettings,$smcFunc;

$today = strtotime("24 hours ago");
date('j') == 1 ? $thismonth = $today : $thismonth = strtotime(date('F') . ' 1');
date('l') == 'Sunday' ? $thisweek = $today : $thisweek = strtotime('last Sunday');
date('M') == 'January' ? $thisyear = $thismonth : $thisyear = strtotime('January 1');

$request = $smcFunc['db_query']('', '
   SELECT
      COUNT(dateRegistered > {string:thisyear} OR NULL) as year,
      COUNT(dateRegistered > {string:thismonth} OR NULL) as month,
      COUNT(dateRegistered > {string:thisweek} OR NULL) as week,
      COUNT(dateRegistered > [sting:today} OR NULL) as today
   FROM {db_prefix}members
   WHERE is_activated = {int:activated}',
   array(
      'activated' => 1,
      'thisyear' => $thisyear,
      'thismonth' => $thismonth,
      'thisweek' => $thisweek,
      'today' => $today,
   ),
); //line 23

settype($row['today'], "integer");
settype($row['week'], "integer");
settype($row['month'], "integer");

while($row = $smcFunc['db_fetch_assoc']($request))
{
   echo 'Total Members: ', $modSettings['totalMembers'],'<br />';
   echo 'New This Month: ', $row['month'],'<br />';
   echo 'New This Week: ', $row['week'],'<br />';
   echo 'New Today: ', $row['today'];
}
// Free the resurce
$smcFunc['db_free_result']($request);
|I Use: SMF 2.0.5 | PortaMx 1.51
Q: What do you do when you see a bear in the woods?
A: Play Dead!

H-A-H-L = Happy Admins Have Linux

Offline Cool

  • Free Tibet
  • PortaMx Supporter
  • *
  • Posts: 744
  • Gender: Male
  • I Hate Chinas Authority
Re: Sql - db_query question
« Reply #9 on: 27.09.08, 14:44:13 »
but don´t stress with that
|I Use: SMF 2.0.5 | PortaMx 1.51
Q: What do you do when you see a bear in the woods?
A: Play Dead!

H-A-H-L = Happy Admins Have Linux

Offline feline

  • CO PortaMx corp.
  • Administrator
  • *
  • Posts: 5786
  • Gender: Female
Re: Sql - db_query question
« Reply #10 on: 27.09.08, 15:24:31 »
Now I have tested that ..
First .. the colnames you use (year, moth ..) used by mysql internal. This MUST enclosed in ` `.
Second .. many colnames in smf 2.0 are changed .. dateRegistered -> date_registered.

Now THIS code is perfect
Code: [Select]
global $modSettings, $smcFunc;

$today = strtotime("24 hours ago");
date('j') == 1 ? $thismonth = $today : $thismonth = strtotime(date('F') . ' 1');
date('l') == 'Sunday' ? $thisweek = $today : $thisweek = strtotime('last Sunday');
date('M') == 'January' ? $thisyear = $thismonth : $thisyear = strtotime('January 1');

$request = $smcFunc['db_query']('', '
   SELECT
      COUNT(date_registered > {int:thisyear} OR NULL) as `year`,
      COUNT(date_registered > {int:thismonth} OR NULL) as `month`,
      COUNT(date_registered > {int:thisweek} OR NULL) as `week`,
      COUNT(date_registered > {int:today} OR NULL) as `today`
   FROM {db_prefix}members
   WHERE is_activated = {int:activated}',
   array(
      'activated' => 1,
      'thisyear' => $thisyear,
      'thismonth' => $thismonth,
      'thisweek' => $thisweek,
      'today' => $today,
   )
);

$row = $smcFunc['db_fetch_assoc']($request);
echo 'Total Members: ', $modSettings['totalMembers'],'<br />';
echo 'New This Year: ', $row['year'],'<br />';
echo 'New This Month: ', $row['month'],'<br />';
echo 'New This Week: ', $row['week'],'<br />';
echo 'New Today: ', $row['today'];

$smcFunc['db_free_result']($request);

You neeed no 'settype' and also no while .. it's always one row  ;)
Result shows as attached image.

Fel

[attachment deleted by admin]
Many are stubborn in relation to the path, a few in relation to the target.

Offline Cool

  • Free Tibet
  • PortaMx Supporter
  • *
  • Posts: 744
  • Gender: Male
  • I Hate Chinas Authority
Re: Sql - db_query question
« Reply #11 on: 27.09.08, 16:08:27 »
THANK YOU SO MUCH
|I Use: SMF 2.0.5 | PortaMx 1.51
Q: What do you do when you see a bear in the woods?
A: Play Dead!

H-A-H-L = Happy Admins Have Linux