0

I work on an bigger database project. Now I become curious, because there are to same SQL-Query, where the first get's an error and the second, same, workes fine. I don't know. Meanwhile I doubt my mind.

Here are the short script part:

$sql    = "SELECT * FROM `wt_name` WHERE `n_surname` LIKE 'A%' AND `n_file` = '4' AND `n_type` = 'NAME' GROUP BY `n_id` ORDER BY `n_surname`, `n_givn` ";

$statement      = $pdo->prepare($sql);
$erg            = $statement->execute();
$result         = $statement->fetchAll(PDO::FETCH_ASSOC);
$anzreihen      = $statement->rowCount();

$sqlANZ = "SELECT * FROM `wt_name` WHERE `n_surname` LIKE 'A%' AND `n_file` = '4' AND `n_type` = 'NAME' GROUP BY `n_id` ORDER BY `n_surname`, `n_givn` ";

$statementANZ   = $pdo->prepare($sqlANZ);
$erg2           = $statementANZ->execute();
$ergANZ         = $statementANZ->fetchAll(PDO::FETCH_ASSOC);
$anzahlGesamt   = $statementANZ->rowCount();

If I'm not blind, $sql and $sqlANZ should be the same query. $sqlANZ workes correct and $erganz get's the datasets it should. But $sql will not work and gives pack the error massage:

Call to a member function prepare() on null

Meanwhile I don't anymore know, what to do. Is there anybody who can check the script and help me, find the error, I seemingly can't see/find?

(only for information: the fist query will be added by a LIMIT, if it works in the actual version at least)

Thanks bagira

bagira41berl
  • 11
  • 3
  • Somewhere between the first and second query you have unset `$pdo`, or perhaps they're in separate files and you never set `$pdo` in the second? – Nick Feb 01 '20 at 22:15
  • Short part of script missed some important information. – u_mulder Feb 01 '20 at 22:16
  • @nick: There are no other script lines between these two queries (it's a complete quote). I set the PDO first in the file. Why can't it work in the first Query but can, whitout any change, work with the second Query? – bagira41berl Feb 01 '20 at 22:19
  • @u_mulder: which part do you need? The PDO is set before and workes fine with the $sqlANZ-Query ... between these two Querys there are no more script lines ... – bagira41berl Feb 01 '20 at 22:21
  • Here you can find a short sample of the table wt_name: https://www.dropbox.com/s/olpnco5lmlg9vfz/wt_name.sql?dl=0 – bagira41berl Feb 01 '20 at 22:51
  • Meanwhile I figured out, that the rowCount-line is the one, which causes the problem. So, try to find out the Count of the Datasets this way: $sql_count = "SELECT COUNT(*) AS COUNT FROM `wt_name` WHERE `n_surname` LIKE 'A%' AND `n_file` = '4' AND `n_type` = 'NAME' GROUP BY `n_id` ORDER BY `n_surname`, `n_givn` LIMIT " . $start . ", " . $limit; – bagira41berl Feb 02 '20 at 01:30
  • $statement_count = $pdo->prepare($sql_count); $erg_count = $statement_count->execute(); $result_count = $statement_count->fetchAll(PDO::FETCH_ASSOC); $anzreihen = $result_count[0]['COUNT']; But unfortunately the Result is the same error "Call to a member function prepare() on null" (happens on the last line), even when the result gives the correct datasets. I'm completely confused ... – bagira41berl Feb 02 '20 at 01:32
  • Ok, it seams like the "GROUP By" may be the problem. Because the COUNT-Query delivers 50 datasets with a number of "1" at the column "COUNT" instead of 1 datasets with a number of "50" at the column "COUNT". But without the "GROUP by" there will be delivered more than 300 datasets (all with "A%" at 'n_surname') instead of 50 (the limit set by the query). Can someone tell me, how to replace the "GROUP By" but get the same result of datasets? – bagira41berl Feb 02 '20 at 01:40

1 Answers1

-2

works this?

$sql    = "
SELECT * 
  FROM `wt_name`
 WHERE  `n_surname`
  LIKE 'A%' 
   AND `n_file` = '4'
   AND `n_type` = 'NAME' 
 GROUP 
    BY `n_id`
 ORDER 
    BY `n_surname`
     , `n_givn`
";

then pdo wants "`" and '4' instead 4

db1975
  • 775
  • 3
  • 9
  • The " and ' were deleted by stackflow during editing my post. So the Query you posted is the same which is used by me. ;) And won't work unfortunately ... – bagira41berl Feb 01 '20 at 22:40
  • rename $sql to another variable. e.g. $sqlFirst. Is there the same result? – db1975 Feb 01 '20 at 23:09
  • 1
    I changed the name to $test. This has no effect. – bagira41berl Feb 01 '20 at 23:14
  • and you defined $pdo before? e.g. $pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass); – db1975 Feb 01 '20 at 23:23
  • Yes. Otherwise the $sqlANZ-Query would'nt work either. – bagira41berl Feb 02 '20 at 00:15
  • its strange :) what is happining if you change the positions of your code parts? first $sqlANZ-Part and than $sql-Part – db1975 Feb 02 '20 at 00:20
  • Nothing changes. The $sql-Query isn't working. – bagira41berl Feb 02 '20 at 01:06
  • Now I figured out, that it's the last line which causes the error: "$anzreihen = $statement->rowCount();". The Query works correct and $result get's the array of the datasets. But rowCount can't run and causes an error. I'll try to do another Query to get the Count now, if it workes this way (curious :D). – bagira41berl Feb 02 '20 at 01:14
  • Now I try to find out the Count of the Datasets this way: $sql_count = "SELECT COUNT(*) AS COUNT FROM `wt_name` WHERE `n_surname` LIKE 'A%' AND `n_file` = '4' AND `n_type` = 'NAME' GROUP BY `n_id` ORDER BY `n_surname`, `n_givn` LIMIT " . $start . ", " . $limit; $statement_count = $pdo->prepare($sql_count); $erg_count = $statement_count->execute(); $result_count = $statement_count->fetchAll(PDO::FETCH_ASSOC); $anzreihen = $result_count[0]['COUNT']; Result is the same ... Error "Call to a member function prepare() on null" on the last line. – bagira41berl Feb 02 '20 at 01:28
  • Ok, it seams like the "GROUP By" may be the problem. Because the COUNT-Query delivers 50 datasets with a number of "1" at the column "COUNT" instead of 1 datasets with a number of "50" at the column "COUNT". But without the "GROUP by" there will be delivered more than 300 datasets (all with "A%" at 'n_surname') instead of 50 (the limit set by the query). Can you tell me, how to replace the "GROUP By" but get the same result of datasets? – bagira41berl Feb 02 '20 at 01:40
  • Here you can find a short sample of the table wt_name: dropbox.com/s/olpnco5lmlg9vfz/wt_name.sql?dl=0 – bagira41berl Feb 02 '20 at 01:41