To use this website completely, it is necessary to store cookies on your computer.
 

* Navigator

Expand - Collapse

* Statistic


  • *Total Posts: 16431
  • *Total Topics: 2522
  • *Online Today: 10
  • *Most Online: 292
(12. Nov 2016, 09:37:31)
  • *Users: 0
  • *Guests: 0
  • *Spiders: 1
  • *Total: 1

  • *MSN (Media)

* Calendar

Mo
Tu
We
Th
Fr
Sa
Su
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

Sql - db_query question

Started by Cool, 25. Sep 2008, 15:25:05

previous topic - next topic

0 Members and 0 Guests are viewing this topic.

Cool

25. Sep 2008, 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

feline

#1
25. Sep 2008, 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.

Cool

#2
25. Sep 2008, 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

feline

#3
26. Sep 2008, 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.

Cool

#4
26. Sep 2008, 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

feline

#5
26. Sep 2008, 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.

Cool

#6
27. Sep 2008, 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

feline

#7
27. Sep 2008, 12:20:05
replace
Code Select
   FROM {$db_prefix}members
with
Code Select
   FROM {db_prefix}members

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

Cool

#8
27. Sep 2008, 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

Cool

#9
27. Sep 2008, 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

feline

#10
27. Sep 2008, 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.

Cool

#11
27. Sep 2008, 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