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
– Strawberry, Commented Aug 21, 2020 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 stackoverflow.com/questions/7537377/… before proceeding with anything else. — Dharman
– Dharman ♦, Commented Aug 21, 2020 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 How to Ask on how to improve the quality of your question. — Progman
– Progman, Commented Aug 28, 2020 at 17:16

nbk · Accepted Answer · 2020-08-29 22:31:07Z

2

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

edited Aug 29, 2020 at 22:31

answered Aug 21, 2020 at 22:27

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

WaGo Over a year ago

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...

nbk Over a year ago

You didn't show,how you call then function, so check the value of $ciEstud if i shpuld gues it is empty

WaGo Over a year ago

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.

nbk Over a year ago

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

WaGo Over a year ago

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.

|

Collectives™ on Stack Overflow

Function to get a value from mysql table

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related