[PHP-DB] RE: RE :[PHP-DB] MySQLi Help
Oliver,
Gah -- thank you.
James
-Original Message-
From: "Olivier Desmares"
Sent: Saturday, June 16, 2012 10:58am
To: "jstarr...@selagodesign.com" ,
"php-db@lists.php.net"
Subject: RE :[PHP-DB] MySQLi Help
Hi James,
The manual page for mysqli_stmt::execute has the following note :
When using mysqli_stmt_execute(), the
[http://fr2.php.net/manual/en/mysqli-stmt.fetch.php] mysqli_stmt_fetch()
function must be used to fetch the data prior to performing any additional
queries.
So you cannot execute the inner (sub) query without first retrieving all the
rows from the outer (main) query.
Olivier Desmares
$stmt = mysqli_prepare($db, $sql);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $authkv);
while (mysqli_stmt_fetch($stmt)) {
echo "MAIN: $authkv";
sub($db);
}
mysqli_stmt_close($stmt);
This simple change results in the output.
MAIN: 7
Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean
given in /var/www/html/xgwebapi/mysqli.php on line 21 Warning:
mysqli_stmt_bind_result() expects parameter 1 to be mysqli_stmt, boolean given
in /var/www/html/xgwebapi/mysqli.php on line 22 Warning: mysqli_stmt_fetch()
expects parameter 1 to be mysqli_stmt, boolean given in
/var/www/html/xgwebapi/mysqli.php on line 24 Warning: mysqli_stmt_close()
expects parameter 1 to be mysqli_stmt, boolean given in
/var/www/html/xgwebapi/mysqli.php on line 28
*
James Starritt
Selago Design, Inc.
US: (312) 239 0592
jstarr...@selagodesign.com
*
Re: [PHP-DB] Re: Database Problems
On Sun, Jun 17, 2012 at 8:22 PM, tamouse mailing lists wrote: > On Sun, Jun 17, 2012 at 5:55 PM, Ethan Rosenberg wrote: >> At 04:21 PM 6/17/2012, Jim Giner wrote: >> >>> "Ethan Rosenberg" wrote in message >>> news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... >>> > At 03:30 PM 6/17/2012, Jim Giner wrote: >>> >>"Ethan Rosenberg" wrote in message >>> >>news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... >>> >> > Dear List - >>> >> > >>> >> >>> >> > >>> >> > The same query in a PHP program will only give me results for MedRec >>> >> > 10003 >>> >> > >>> >> >>> >>why the "where 1" clause? Do you know what that is for? >>> > = >>> > Dear Jim >>> > >>> > Thanks >>> > >>> > As I understand, to enable me to concatenate phases to construct a >>> > query. >>> > >>> > The query does work in MySQL fro the terminal. >>> > >>> > Ethan >>> > >>> >>> I don't think so. All it does is return one record. The where clause >>> defines what you want returned. A '1' returns one record, the first one. >>> #10003 >>> >>> I wonder why you think "where 1" enables concatenation?? A query (IN >>> SIMPLE >>> TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with >>> a >>> WHERE clause to define the criteria to limit the rows, and an ORDER BY to >>> sort the result set. More complex queries can include GROUP BY when you >>> are >>> including summary operators such as SUM(fldname) or MAX(fldname), or a >>> JOIN >>> clause, or a host of other clauses that make sql so powerful. In your >>> case >>> I think you copied a sample query that (they always seem to be displayed >>> with a 'where 1' clause) and left the "1" on it. To summarzie: >>> >>> SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key >>> >100 >>> and a.key = b.key ORDER BY a.fld1 >>> >>> I"m no expert, but hopefully this makes it a little less complex for you. >> >> >> >> This is a suggestion I received from this list - >> + $query = "select * from Intake3 where "; >>> >>> >>> Maybe I missed it, but you need to have a 1 after the where part in your >>> select. So... >>> >>> $query = "SELECT * FROM Intake3 WHERE 1 "; >> >> >> >> I must stress that the query works from the terminal, and fails in the PHP >> code. >> >> My question is why? >> >> Ethan > > First off, all "where 1" does is make the statement always true. If > you don't believe that, try this query: > > SELECT * FROM Intake3 WHERE 1; > > in mysql and contrast with: > > SELECT * FROM Intake3; > > you should see identical results. > > Secondly, you need to look at where you are gathering the return from > the query. You show: > > $result = mysqli_query($cxn,$query); > > but you aren't showing us what you do with $result, as in where you > fetch the rows and process them. I'm dubious that the query is in fact > only returning one record. But you can check by echoing > mysqli_num_rows($result). (I think; I never use the procedural > version, opting to use the object version instead.) Just to clarify, what Ethan has is not incorrect: the way he's constructing the where clause requires at least one expression before the $allowed_fields gets tacked on: > $query .= " AND ($key = '$val') "; Since if even one of those gets appended, there must be an expression before the first "AND". The 1 merely evaluates to true here, creating a valid where clause. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Sun, Jun 17, 2012 at 5:55 PM, Ethan Rosenberg wrote: > At 04:21 PM 6/17/2012, Jim Giner wrote: > >> "Ethan Rosenberg" wrote in message >> news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... >> > At 03:30 PM 6/17/2012, Jim Giner wrote: >> >>"Ethan Rosenberg" wrote in message >> >>news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... >> >> > Dear List - >> >> > >> >> >> >> > >> >> > The same query in a PHP program will only give me results for MedRec >> >> > 10003 >> >> > >> >> >> >>why the "where 1" clause? Do you know what that is for? >> > = >> > Dear Jim >> > >> > Thanks >> > >> > As I understand, to enable me to concatenate phases to construct a >> > query. >> > >> > The query does work in MySQL fro the terminal. >> > >> > Ethan >> > >> >> I don't think so. All it does is return one record. The where clause >> defines what you want returned. A '1' returns one record, the first one. >> #10003 >> >> I wonder why you think "where 1" enables concatenation?? A query (IN >> SIMPLE >> TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with >> a >> WHERE clause to define the criteria to limit the rows, and an ORDER BY to >> sort the result set. More complex queries can include GROUP BY when you >> are >> including summary operators such as SUM(fldname) or MAX(fldname), or a >> JOIN >> clause, or a host of other clauses that make sql so powerful. In your >> case >> I think you copied a sample query that (they always seem to be displayed >> with a 'where 1' clause) and left the "1" on it. To summarzie: >> >> SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key >> >100 >> and a.key = b.key ORDER BY a.fld1 >> >> I"m no expert, but hopefully this makes it a little less complex for you. > > > > This is a suggestion I received from this list - > + >>> >>> $query = "select * from Intake3 where "; >> >> >> Maybe I missed it, but you need to have a 1 after the where part in your >> select. So... >> >> $query = "SELECT * FROM Intake3 WHERE 1 "; > > > > I must stress that the query works from the terminal, and fails in the PHP > code. > > My question is why? > > Ethan First off, all "where 1" does is make the statement always true. If you don't believe that, try this query: SELECT * FROM Intake3 WHERE 1; in mysql and contrast with: SELECT * FROM Intake3; you should see identical results. Secondly, you need to look at where you are gathering the return from the query. You show: $result = mysqli_query($cxn,$query); but you aren't showing us what you do with $result, as in where you fetch the rows and process them. I'm dubious that the query is in fact only returning one record. But you can check by echoing mysqli_num_rows($result). (I think; I never use the procedural version, opting to use the object version instead.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
"Matijn Woudt" wrote in message news:cac_gtup8fjuv7jyut89w2491nm-7zno8mrj3w0mep6totm2...@mail.gmail.com... On Sun, Jun 17, 2012 at 10:21 PM, Jim Giner wrote: > > "Ethan Rosenberg" wrote in message > news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... >> At 03:30 PM 6/17/2012, Jim Giner wrote: >>>"Ethan Rosenberg" wrote in message >>>news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... >>> > Dear List - >>> > >>> >>> > >>> > The same query in a PHP program will only give me results for MedRec >>> > 10003 >>> > >>> >>>why the "where 1" clause? Do you know what that is for? >> = >> Dear Jim >> >> Thanks >> >> As I understand, to enable me to concatenate phases to construct a query. >> >> The query does work in MySQL fro the terminal. >> >> Ethan >> > > I don't think so. All it does is return one record. The where clause > defines what you want returned. A '1' returns one record, the first one. > #10003 > > I wonder why you think "where 1" enables concatenation?? A query (IN > SIMPLE > TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with > a > WHERE clause to define the criteria to limit the rows, and an ORDER BY to > sort the result set. More complex queries can include GROUP BY when you > are > including summary operators such as SUM(fldname) or MAX(fldname), or a > JOIN > clause, or a host of other clauses that make sql so powerful. In your case > I think you copied a sample query that (they always seem to be displayed > with a 'where 1' clause) and left the "1" on it. To summarzie: > > SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key > >100 > and a.key = b.key ORDER BY a.fld1 > > I"m no expert, but hopefully this makes it a little less complex for you. > Right, Why would WHERE 1 return only 1 record? That makes no sense and, no offense, it sounds like you really don't know where you're talking about. WHERE 1 is just a useless statement, but in his case makes it easier to concatenate multiple "AND ..." statements. As to the original problem, I guess that there might be something wrong with your code later on that parses the result? Did you try to use echo mysqli_num_rows($result1), just after the query to see how many rows it returned? - Matijn You could be right. My interpretation of "where 1" is it returns one record. And as I said I'm no expert. I guess where 1 could mean "where true". -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
On Sun, Jun 17, 2012 at 10:21 PM, Jim Giner wrote: > > "Ethan Rosenberg" wrote in message > news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... >> At 03:30 PM 6/17/2012, Jim Giner wrote: >>>"Ethan Rosenberg" wrote in message >>>news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... >>> > Dear List - >>> > >>> >>> > >>> > The same query in a PHP program will only give me results for MedRec >>> > 10003 >>> > >>> >>>why the "where 1" clause? Do you know what that is for? >> = >> Dear Jim >> >> Thanks >> >> As I understand, to enable me to concatenate phases to construct a query. >> >> The query does work in MySQL fro the terminal. >> >> Ethan >> > > I don't think so. All it does is return one record. The where clause > defines what you want returned. A '1' returns one record, the first one. > #10003 > > I wonder why you think "where 1" enables concatenation?? A query (IN SIMPLE > TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a > WHERE clause to define the criteria to limit the rows, and an ORDER BY to > sort the result set. More complex queries can include GROUP BY when you are > including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN > clause, or a host of other clauses that make sql so powerful. In your case > I think you copied a sample query that (they always seem to be displayed > with a 'where 1' clause) and left the "1" on it. To summarzie: > > SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key >100 > and a.key = b.key ORDER BY a.fld1 > > I"m no expert, but hopefully this makes it a little less complex for you. > Right, Why would WHERE 1 return only 1 record? That makes no sense and, no offense, it sounds like you really don't know where you're talking about. WHERE 1 is just a useless statement, but in his case makes it easier to concatenate multiple "AND ..." statements. As to the original problem, I guess that there might be something wrong with your code later on that parses the result? Did you try to use echo mysqli_num_rows($result1), just after the query to see how many rows it returned? - Matijn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
At 04:21 PM 6/17/2012, Jim Giner wrote: "Ethan Rosenberg" wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... > At 03:30 PM 6/17/2012, Jim Giner wrote: >>"Ethan Rosenberg" wrote in message >>news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... >> > Dear List - >> > >> >> > >> > The same query in a PHP program will only give me results for MedRec >> > 10003 >> > >> >>why the "where 1" clause? Do you know what that is for? > = > Dear Jim > > Thanks > > As I understand, to enable me to concatenate phases to construct a query. > > The query does work in MySQL fro the terminal. > > Ethan > I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think "where 1" enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the "1" on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key >100 and a.key = b.key ORDER BY a.fld1 I"m no expert, but hopefully this makes it a little less complex for you. This is a suggestion I received from this list - + $query = "select * from Intake3 where "; Maybe I missed it, but you need to have a 1 after the where part in your select. So... $query = "SELECT * FROM Intake3 WHERE 1 "; I must stress that the query works from the terminal, and fails in the PHP code. My question is why? Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
first rule of computer science: if everything else fails, read the manual!!!or short: RTFM! --- Regards DZvonko Nikolov dzvo...@gmail.com dzvo...@yahoo.com --- The best things are simple, but finding these simple things is not simple. Simplicity carried to the extreme becomes elegance. ---
Re: [PHP-DB] Re: Database Problems
"Ethan Rosenberg" wrote in message news:0m5s00mgd2bh7...@mta1.srv.hcvlny.cv.net... > At 03:30 PM 6/17/2012, Jim Giner wrote: >>"Ethan Rosenberg" wrote in message >>news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... >> > Dear List - >> > >> >> > >> > The same query in a PHP program will only give me results for MedRec >> > 10003 >> > >> >>why the "where 1" clause? Do you know what that is for? > = > Dear Jim > > Thanks > > As I understand, to enable me to concatenate phases to construct a query. > > The query does work in MySQL fro the terminal. > > Ethan > I don't think so. All it does is return one record. The where clause defines what you want returned. A '1' returns one record, the first one. #10003 I wonder why you think "where 1" enables concatenation?? A query (IN SIMPLE TERMS PEOPLE) is simply a SELECT ion of fields from a group of tables,with a WHERE clause to define the criteria to limit the rows, and an ORDER BY to sort the result set. More complex queries can include GROUP BY when you are including summary operators such as SUM(fldname) or MAX(fldname), or a JOIN clause, or a host of other clauses that make sql so powerful. In your case I think you copied a sample query that (they always seem to be displayed with a 'where 1' clause) and left the "1" on it. To summarzie: SELECT a.fld1, a.fld2,b.fld1 from table1 as a, table2 as b WHERE a.key >100 and a.key = b.key ORDER BY a.fld1 I"m no expert, but hopefully this makes it a little less complex for you. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Database Problems
At 03:30 PM 6/17/2012, Jim Giner wrote: "Ethan Rosenberg" wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... > Dear List - > > > The same query in a PHP program will only give me results for MedRec 10003 > why the "where 1" clause? Do you know what that is for? = Dear Jim Thanks As I understand, to enable me to concatenate phases to construct a query. The query does work in MySQL fro the terminal. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Database Problems
"Ethan Rosenberg" wrote in message news:0m5r005qyzrnm...@mta6.srv.hcvlny.cv.net... > Dear List - > > > The same query in a PHP program will only give me results for MedRec 10003 > why the "where 1" clause? Do you know what that is for? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Database Problems
Dear List -
I have a database:
+-+
| Tables_in_hospital2 |
+-+
| Intake3 |
| Visit3 |
+-+
mysql> describe Intake3;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| Site | varchar(6) | NO | PRI | | |
| MedRec | int(6) | NO | PRI | NULL| |
| Fname | varchar(15) | YES | | NULL| |
| Lname | varchar(30) | YES | | NULL| |
| Phone | varchar(30) | YES | | NULL| |
| Height | int(4) | YES | | NULL| |
| Sex| char(7) | YES | | NULL| |
| Hx | text| YES | | NULL| |
++-+--+-+-+---+
mysql> describe Visit3;
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-++
| Indx | int(4) | NO | PRI | NULL| auto_increment |
| Site | varchar(6) | YES | | NULL||
| MedRec | int(6) | YES | | NULL||
| Notes | text | YES | | NULL||
| Weight | int(4) | YES | | NULL||
| BMI| decimal(3,1) | YES | | NULL||
| Date | date | YES | | NULL||
++--+--+-+-++
mysql> mysql> select * from Intake3 where 1 AND (Site =
'AA') AND (Sex = 'Male') ;
+--++-+---+--++--+---+
| Site | MedRec | Fname | Lname | Phone| Height |
Sex | Hx|
+--++-+---+--++--+---+
| AA | 10003 | Stupid | Fool | 325 563-4178 | 65 | Male
| Has been convinced by his friends that he is obese. Normal
BMI = 23. |
| AA | 1 | David | Dummy | 845 365-1456 | 66 | Male
| c/o obesity. Various treatments w/o success |
| AA | 10001 | Tom | Smith | 984 234-4586 | 68 | Male
| BMI = 20. Thinks he is obese. |
| AA | 10007 | Foolish | Fool | 456 147-321 | 60 | Male
| Thinks he is thin. BMI = 45 |
| AA | 10005 | Tom | Obstinant | 845 368-2244 | 66 | Male
| Insists that he is not fat. Becomes violent. Psych involved.|
| AA | 10015 | Dim | Wit | 321 659-3111 | 70 | Male
| Very Tall |
| AA | 10040 | Bongish | Bongish | 123 456-7890 | 50 | Male
| Bong |
+--++-+---+--++--+-
The same query in a PHP program will only give me results for MedRec 10003
$allowed_fields = array
( 'Site' =>$_POST['Site'], 'MedRec' =>
$_POST['MedRec'], 'Fname' => $_POST['Fname'], 'Lname' => $_POST['Lname'] ,
'Phone' => $_POST['Phone'] , 'Sex' => $_POST['Sex'] ,
'Height' => $_POST['Height'] );
if(empty($allowed_fields))
{
echo "ouch";
}
$query = "select * from Intake3 where 1 ";
foreach ( $allowed_fields as $key => $val )
{
if ( (($val != '')) )
{
$query .= " AND ($key = '$val') ";
}
$result1 = mysqli_query($cxn, $query);
}
Ethan
MySQL 5.1 PHP 5.3.3-6 Linux [Debian (sid)]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
