Query can't find results in PHP code but works on PHPmyAdmin

Question

I have a project developed with PHP and mySQL, and I have a query that doesn't work in the PHP code, but when i try the same query on PHPmyAdmin it works without problems (it find results).

The code is the next:

        $sqlD="SELECT Rut FROM tbl110_dotacion 
        WHERE Rut='$rut' AND FecTurno='$fecTurno'";
        $requestD=mysqli_query($connect,$sqlD);
        echo $sqlD,'--->';
        while($rowx=mysqli_fetch_array($requestD)){
        $RUTS=$rowx['Rut'];
        echo $RUTS,'<br>';
        }

The code should print the "rut" variable.

this is the connection, above the query

$server="localhost";
$user="andes";
$pass="password";
$DB="andes";
$connect=mysqli_connect($server, $user, $pass, $DB);

Table structure:

CREATE TABLE tbl110_dotacion` 
( FecTurno varchar(10) DEFAULT NULL, 
Rut varchar(11) DEFAULT NULL, 
CodTurno varchar(5) DEFAULT NULL, 
Ausencia varchar(9) DEFAULT NULL, 
Atraso varchar(9) DEFAULT NULL, 
Accidente varchar(9) DEFAULT NULL, 
Enfermedad varchar(9) DEFAULT NULL,
HHEE varchar(9) DEFAULT NULL, 
Policlinico varchar(5) DEFAULT NULL,
Ausentismo_RRHH varchar(5) DEFAULT NULL, 
CodCargo varchar(5) DEFAULT NULL, 
No_Colacion varchar(9) DEFAULT NULL, 
seleccion varchar(5) DEFAULT NULL ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Are you sure that the application is connecting to the database properly ? — yardie, Feb 25 '16 at 13:16
in while loop print_r($rowx) and check if you get any data, if not, there could be issue with database connection. — Peeje, Feb 25 '16 at 13:18
Yes, above in the same code, i execute queries and they works without problems and the connection is the same — Andes, Feb 25 '16 at 13:18
Andes, in PHPMyAdmin please **export** the table (top tab button) and export it (save it as a SQL file), then opening the SQL file at the top of the file will be the MySQL code for creating the table (*not* the data contents) and please post this table construction data . thanks — Martin, Feb 25 '16 at 13:26
@Martin `CREATE TABLE `tbl110_dotacion` ( `FecTurno` varchar(10) DEFAULT NULL, `Rut` varchar(11) DEFAULT NULL, `CodTurno` varchar(5) DEFAULT NULL, `Ausencia` varchar(9) DEFAULT NULL, `Atraso` varchar(9) DEFAULT NULL, `Accidente` varchar(9) DEFAULT NULL, `Enfermedad` varchar(9) DEFAULT NULL, `HHEE` varchar(9) DEFAULT NULL, `Policlinico` varchar(5) DEFAULT NULL, `Ausentismo_RRHH` varchar(5) DEFAULT NULL, `CodCargo` varchar(5) DEFAULT NULL, `No_Colacion` varchar(9) DEFAULT NULL, `seleccion` varchar(5) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;` — Andes, Feb 25 '16 at 13:39
you have a wayward backtick here: `CREATE TABLE tbl110_dotacion\` `. Is this a typo? — Martin, Feb 25 '16 at 13:48
@Andes as long as your table is not named with a backtick (`\``) in the name! — Martin, Feb 25 '16 at 14:12
the text editor here modifed my comment CREATE TABLE 'tbl110_dotacion' ( — Andes, Feb 25 '16 at 14:15
ok @Andes see my answer, work through my points and let me know if that doesn't fix your problem. — Martin, Feb 25 '16 at 14:24

Dharmesh patel · Answer 1 · 2016-02-25T13:28:29.003

0

try something like this:

    $sqlD="SELECT Rut,FecTurno FROM tbl110_dotacion 
    WHERE Rut='$rut' AND FecTurno='$fecTurno'";

    $requestD=mysqli_query($connect,$sqlD);
    echo $sqlD;
    while($rowx=mysqli_fetch_array($requestD)){
    $RUTS=$rowx['Rut'];
    echo $RUTS.'<br>';
    }

May be work.

edited Feb 25 '16 at 13:28

answered Feb 25 '16 at 13:19

Dharmesh patel

654
1
12
23

How adding a new column name in select query would make it work? – Peeje Feb 25 '16 at 13:22
@peeje user used it in where condition so, may be without it cause problem. – Dharmesh patel Feb 25 '16 at 13:24
Nope, it cannot be! You can use any number of columns in where condition and fetch the different set of columns. – Peeje Feb 25 '16 at 13:26
@Dharmeshpatel not necessary that we have to select all the columns which are in `where` condition – Pathik Vejani Feb 25 '16 at 13:26

score 0 · Answer 2 · edited Feb 25 '16 at 13:30

0

One possible cause could be the column names(Camel-Case). Try this:

"SELECT rut FROM tbl110_dotacion 
    WHERE rut='$rut' AND fecturno='$fecTurno'"

edited Feb 25 '16 at 13:30

Martin

22,212
11
70
132

answered Feb 25 '16 at 13:24

Peeje

445
2
9

This was my thought too. – Martin Feb 25 '16 at 13:25
the names are ok, because it works in PHPmyAdmin but not in the PHP code – Andes Feb 25 '16 at 13:26
In certain cases phpMyAdmin ignore the cases, try if lowering the case make it work. – Peeje Feb 25 '16 at 13:28
@Peeje I lowered the case, but still not giving result – Andes Feb 25 '16 at 13:44
what print_r($rowx) returns? is there any value? – Peeje Feb 25 '16 at 13:45

score 0 · Answer 3 · answered Feb 25 '16 at 13:27

0

Print the $requestD value and see the return result you are getting.

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

http://php.net/manual/en/mysqli.query.php

answered Feb 25 '16 at 13:27

Sk_

1,051
8
18

`print_r` should be used rather than standard print. – Martin Feb 25 '16 at 13:29
this is what print_r gives `mysqli_result Object ( [current_field] => 0 [field_count] => 1 [lengths] => [num_rows] => 0 [type] => 0 )` – Andes Feb 25 '16 at 13:36
[num_rows] is 0. The query is executing correctly but not returning any rows from the table. Can you replace the $sqlD with the phpmyadmin query and see any result is fetching – Sk_ Feb 25 '16 at 13:48
@Sk_ I made what you said. With phpmyadmin query it returned results, but when I replaced the values with the variables $rut and $fecturno, it doesn't give any value from dotacion table, even when this are valid variables – Andes Feb 25 '16 at 14:04

score 0 · Answer 4 · edited May 23 '17 at 11:50

Ok, process for defining where the error occurs:

1 Connection

Check that your PHP does successfully connect to the MySQL server. (this has been done)

2 Variables

Check your PHP variables:

$rut and $fecTurno, print these values before the SQL query to prove to yourself that they're the correct values as set by the page.

3 CamelCase

Check your Case Sensitivity as referenced by Peeje, MySQL is case sensitive. (this has been done)

4 Variabe Escaping

It works in one medium, PHPMyAdmin but not in your PHP page so next stop is to check what's different, have you escaped your $variables? to remove backticks, single quotes and other undesirable characters?

Use Mysqli_real_escape_string to do this.

5 Character Sets

Check your character encoding. You need to define your character encodings correctly. This is only a possible cause, depending on the exact contents of your various PHP variables, but judging by the wordings your native language probably uses the extended UTF-8 character set and not the MySQL UTF-8 character set. Please read: UTF-8 all the way through

MySQL UTF-8 is VERY BAD, you want to instead use the MySQL utf8mb4 encoding which acts as UTF-8 should. Read the link for the details as to why.

Also on your connection file set the connection character set, this is different from the storage character set:

 mysqli_set_charset($connect, "utf8mb4");

http://php.net/manual/en/mysqli.set-charset.php

Note that MySQL does not speak the same language as everyone else. When MySQL says "utf8" it really means "some weirdly retarded variant of UTF-8 that is limited to three bytes for god knows what ridiculous reason". If you really want UTF-8 you should tell MySQL that you want this weird thing MySQL likes to call utf8mb4. Don't bother saving on the "WTF!"s.

From comments on the link above.

Hopefully working through the above will iron out your issue.

I realized that my php mail function stopped to work too [link]http://stackoverflow.com/questions/35633725/php-mail-stop-working-with-gmail-on-localhost-site — Andes, Feb 25 '16 at 17:37
Hello @Andes . Then this shows that the problem is outside of the scope of what you have in your question, please edit your question and show: 1) An example of the expected output (the data not just the variable). And state what version of MySQL you run, what version of PHPMyAdmin you run as well as what version of PHP you are running. Cheers — Martin, Feb 25 '16 at 21:26

Query can't find results in PHP code but works on PHPmyAdmin

4 Answers4