Function to get a value from mysql table

Question

I have this php function:

function getCredEstud(int $ciEstud) {
   include_once 'conexion.php';
   $result = mysqli_query($conexion,"SELECT DISTINCT `creditosEstudiante` FROM `op_JOIN_est-insc-mat` WHERE ciEstudiante = '".$ciEstud."'");
   $i=0;
   while($row = mysqli_fetch_array($result)) {
       return (int)$row["creditosEstudiante"];
       $i++;
   }
   mysqli_close($conexion);
}

which should get the value of "creditosEstudiantes" from a given "ciEstudiante" of the below table.

The problem is that it return nothing.

Could someone tell me what i am doing wrong?

Thanks in advance! =)

randomNum   ciEstudiante    creditosEstudiante  codigoMateria   tipoMateria cupoMateria creditosMateria     
0   1780859     75  1035    CURRICULAR  0   3
0   1780859     75  1954    OPTATIVA    25  0
0   1780859     75  1025    CURRICULAR  0   3
0   1780859     75  1910    OPTATIVA    LIBRE   0
0   1780859     75  1948    OPTATIVA    60  0
0   1780859     75  2105P   CURRICULAR  0   4
0   1780859     75  2100P   CURRICULAR  0   4
0   1780859     75  2095    CURRICULAR  0   3
0   1780859     75  4170    CURRICULAR  0   3
0   1780859     75  1932    OPTATIVA    30  0
0   1780859     75  3140    CURRICULAR  0   4
0   1780859     75  2190    CURRICULAR  0   4
0   1780859     75  2115P   CURRICULAR  0   4
0   1921177     93  2115P   CURRICULAR  0   4
0   1921177     93  2095    CURRICULAR  0   3
0   1921177     93  2105P   CURRICULAR  0   4
0   1921177     93  2100P   CURRICULAR  0   4
0   2020703     269     3140    CURRICULAR  0   4
0   2020703     269     4160    CURRICULAR  0   3
0   2762533     249     3020P   CURRICULAR  0   28
0   2971526     355     1954    OPTATIVA    25  0
0   3102055     55  1021    CURRICULAR  0   25
0   3102055     55  2095    CURRICULAR  0   3

Here is the full code:

    <?php

function getRandomCIEstud() {
   include_once 'conexion.php';
   $result = mysqli_query($conexion,"SELECT DISTINCT `ciEstudiante` FROM `op_JOIN_est-insc-mat` ORDER BY RAND() LIMIT 1");
   $i=0;
   while($row = mysqli_fetch_array($result)) {
       //$selectedCI=$row["ciEstudiante"];
       return $RandomCIEstud=$row["ciEstudiante"];
       //echo $RandomCIEstud=(int)$row["ciEstudiante"];
       $i++;
   }
   mysqli_close($conexion);
}


function getCredEstud(int $ciEstud) {
   include_once 'conexion.php';
   $where = 'WHERE `ciEstudiante` = ?';
    if (empty($ciEstud)) {
        $ciEstud = 0;
        $where = 'ORDER BY RAND() LIMIT 1';
    }
   $sql = "SELECT DISTINCT `creditosEstudiante` FROM `op_JOIN_est-insc-mat` ".$where;  
   //result is boolean for query other than SELECT, SHOW, DESCRIBE and EXPLAIN
   if ($stmt = mysqli_prepare($conexion, $sql)) {

       // bind parameters for markers
       if ($ciEstud <> 0) {
            //mysqli_stmt_bind_param($stmt, "s", $ciEstud);
            mysqli_stmt_bind_param($stmt, "i", $ciEstud);
       }

      // execute query
      mysqli_stmt_execute($stmt);

     // bind result variables
     mysqli_stmt_bind_result($stmt, $result);

     // fetch values
     $i=0;
     while (mysqli_stmt_fetch($stmt)) {
        return (int)$result;
        $i++;
     }
    if ($i==0) {
        // if no entry is found return -1
        return -1;
    }
    // close statement
    mysqli_stmt_close($stmt);
 } else {
      printf("Error: %s.\n", mysqli_stmt_error($stmt));
 }
 mysqli_close($conexion);
}   


?>
<!DOCTYPE html>
<html>
<head>
<title> Retrive data</title>
</head>
<body>

<table>
<tr>
<td>getRandomCIEstud(): <?php $randomCI=getRandomCIEstud(); echo $randomCI; ?></td>
</tr>
</table>

<table>
<tr>
<td>getCredEstud(): <?php echo getCredEstud((int)$randomCI); ?></td>
</tr>
</table>

</body>
</html>

This is the code I have so far. I tried everithing but it doesnt work right now. It doesnt show any error, it just dont return anything. I guess It is something wrong with the way I am passing the parameters.

Thank you all very much for your help

Couple of points: including mathematical operators in table names is a cataclysmically bad idea, and see about sql injection — Strawberry, Aug 21 '20 at 20:36
While technically this piece of code is not vulnerable to SQL injection, it shows that you have no idea how to include PHP variable in SQL. I recommend you read https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement before proceeding with anything else. — Dharman, Aug 21 '20 at 21:05
Do not add nonsense to your question to avoid the system which is put in place. Instead add a more detailed description of what you want to do. Please read [ask] on how to improve the quality of your question. — Progman, Aug 28 '20 at 17:16

nbk · Answer 1 · 2020-08-29T22:31:07.340

CREATE TABLE `op_JOIN_est-insc-mat` (
  `randomNum` INTEGER,
  `ciEstudiante` INTEGER,
  `creditosEstudiante` INTEGER,
  `codigoMateria` VARCHAR(5),
  `tipoMateria cupoMateria creditosMateria` VARCHAR(10)
);

INSERT INTO `op_JOIN_est-insc-mat` 
  (`randomNum`, `ciEstudiante`, `creditosEstudiante`, `codigoMateria`
  , `tipoMateria cupoMateria creditosMateria`)
VALUES
  ('0', '1780859', '75', '1035', 'CURRICULAR'),
  ('0', '1780859', '75', '1954', 'OPTATIVA'),
  ('0', '1780859', '75', '1025', 'CURRICULAR'),
  ('0', '1780859', '75', '1910', 'OPTATIVA'),
  ('0', '1780859', '75', '1948', 'OPTATIVA'),
  ('0', '1780859', '75', '2105P', 'CURRICULAR'),
  ('0', '1780859', '75', '2100P', 'CURRICULAR'),
  ('0', '1780859', '75', '2095', 'CURRICULAR'),
  ('0', '1780859', '75', '4170', 'CURRICULAR'),
  ('0', '1780859', '75', '1932', 'OPTATIVA'),
  ('0', '1780859', '75', '3140', 'CURRICULAR'),
  ('0', '1780859', '75', '2190', 'CURRICULAR'),
  ('0', '1780859', '75', '2115P', 'CURRICULAR'),
  ('0', '1921177', '93', '2115P', 'CURRICULAR'),
  ('0', '1921177', '93', '2095', 'CURRICULAR'),
  ('0', '1921177', '93', '2105P', 'CURRICULAR'),
  ('0', '1921177', '93', '2100P', 'CURRICULAR'),
  ('0', '2020703', '269', '3140', 'CURRICULAR'),
  ('0', '2020703', '269', '4160', 'CURRICULAR'),
  ('0', '2762533', '249', '3020P', 'CURRICULAR'),
  ('0', '2971526', '355', '1954', 'OPTATIVA'),
  ('0', '3102055', '55', '1021', 'CURRICULAR'),
  ('0', '3102055', '55', '2095', 'CURRICULAR');

SELECT DISTINCT `ciEstudiante` INTO @id FROM `op_JOIN_est-insc-mat` ORDER BY RAND() LIMIT 1

✓

SELECT DISTINCT `creditosEstudiante` 
FROM `op_JOIN_est-insc-mat`
WHERE `ciEstudiante` = @id

| creditosEstudiante |
| -----------------: |
|                269 |

db<>fiddle here

But your code is vulnerable to sql injection so use only prepared statements with parameters

As you can see i rewrote your code, but it looks like you don't expect more than one result (because of the return ) from your query as shown in the Select example abouve, but still you use a while loop and don't limit the result to 1

So you want to check that

 function getCredEstud(int $ciEstud) {
   include_once 'conexion.php';
   $where = 'WHERE `ciEstudiante` = ?';
    if (empty($ciEstud)) {
        $ciEstud = 0;
        $where = 'ORDER BY RAND() LIMIT 1';
    }
   $sql = "SELECT DISTINCT `creditosEstudiante` FROM `op_JOIN_est-insc-mat` ".$where;  
   //result is boolean for query other than SELECT, SHOW, DESCRIBE and EXPLAIN
   if ($stmt = mysqli_prepare($conexion, $sql)) {

       /* bind parameters for markers */
       if ($ciEstud <> 0) {
            mysqli_stmt_bind_param($stmt, "s", $ciEstud);
       }

      /* execute query */
      mysqli_stmt_execute($stmt);

     /* bind result variables */
     mysqli_stmt_bind_result($stmt, $result);

     /* fetch values */
     $i=0;
     while (mysqli_stmt_fetch($stmt)) {
        return (int)$result;
        $i++;
     }
    if ($i==0) {
        /* if no entry is found return -1  */
        return -1;
    }
    /* close statement */
    mysqli_stmt_close($stmt);
 } else {
      printf("Error: %s.\n", mysqli_stmt_error($stmt));
 }
 mysqli_close($conexion);
}

Change Your query to

SELECT `creditosEstudiante` FROM `op_JOIN_est-insc-mat` ORDER BY RAND() LIMIT 1

Which will a random creditosEstudiante, if that is what you want

Your logic is off

 `<td>getRandomCIEstud(): <?php $myid = getRandomCIEstud(); echo $myid;?></td> 
 <td>getCredEstud(): <?php echo getCredEstud((int)$myid); ?></td>`

As you see you have to save the id you get to run it throw your function else it will give you different random number

It doesn't bring any data (but it should bring data). It doesn't show any error either, it works like the function I wrote... — WaGo, Aug 26 '20 at 23:09
You didn't show,how you call then function, so check the value of $ciEstud if i shpuld gues it is empty — nbk, Aug 26 '20 at 23:14
I post the full code. I guess the same as you. But I test it and it seems to works fine... Thank you very much for all your help. — WaGo, Aug 27 '20 at 15:04
i add to my question this `getCredEstud((int)getRandomCIEstud())`can't reurn anything because your function expects a ciEstudiante any but you don't delivers any so th the routione search for ciEstudiante = 0 which has no result. In your case use my query at the end of my answer — nbk, Aug 27 '20 at 15:14
As I see you "join" both functions into one. I guess it will work but I need to make it work the way I wrote it. That is, to find a random CI (student document number) from the table. For that I wrote the "getRandomCIEstud" function. That one works. So no problem there. Then I need to pass that number I found (the random CI) to the other function (getCredEstud) in order to get the number of credits for that random student. So I really needs that works that way: one function find the random ci (getRandomCIEstud) and pass that number to the other function (getCredEstud). Thank you. — WaGo, Aug 27 '20 at 23:41
i chnged the function, this will give you a creditosEstudiante for a specific $ciEstud , if you don't give3 onbe as in your code it picks a random. I think from here you can solve your problem — nbk, Aug 28 '20 at 11:12
Im sorry but it is not working. The output I get is this: getRandomCIEstud(): 4791506 getCredEstud(): Error: . I calling the functions like this: getRandomCIEstud(): and getCredEstud(): Thank you — WaGo, Aug 28 '20 at 16:35
I see what you mean and you are right. but that did not make the diference because it wont gave a wrong number, it dont return anything, I pasted the right now code with all the changes we made and if you run it you will see the problem... — WaGo, Aug 29 '20 at 22:04
sorry i don't see it, what i did in the changed dbfidle is that whqat we, do in the code. so you shoud have in the first echo a number and the second should also give a number as you can see in teh dbfiddle. When not add a many echo as you need to debug your code especially if the second function gets a number and uses it — nbk, Aug 29 '20 at 22:33

Function to get a value from mysql table

1 Answers1