Issue with connecting to mysql db on myphpadmin

General PHP Help
#1

Hi, im a total newbie and i’ve been struggling to understand PHP by reading someone’s else code that was not working (less than ideal but it is what it is).

I’ve made changes to the mysql database by creating new tables, updating names and trying to make everything run smooth. This is for a psychology experiment where subjects need to see an image, answer yes/no and rank from 0-100 the risk level.

I’m having issues in understanding why it’s not saving to the mysql database.
It’s giving “error Warning: mysqli_query() expects parameter 1 to be mysqli” error on lines
90,101 and 104

90 - if (mysqli_query($conn,$sql_user) === TRUE) {
101 - $result_user = mysqli_query($conn, $sql_user_query);
104 - while($row = mysqli_fetch_array($result_user))

Thanks in advance

<!DOCTYPE html>

<html lang="en">

<head>

    <meta charset="utf-8">

    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <meta name="description" content="">

    <meta name="author" content="">

    <title>Estudio Trafico v2</title>

    <!-- Bootstrap core CSS -->

    <link href="vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">

    <link href="mycss.css" rel="stylesheet">

    <!-- Custom styles for this template -->

    <style>

    body {

        padding-top: 54px;

    }

   

    @media (min-width: 992px) {

        body {

            padding-top: 56px;

        }

    }

    /* Temporary navbar container fix */

   

    .navbar-toggler {

        z-index: 1;

    }

   

    @media (max-width: 576px) {

        nav > .container {

            width: 100%;

        }

    }

    </style>

</head>

<body>

    <!-- Navigation -->

    <nav class="navbar fixed-top navbar-toggleable-md navbar-inverse bg-inverse">

        <button class="navbar-toggler navbar-toggler-right" type="button" data-toggle="collapse" data-target="#navbarExample" aria-controls="navbarExample" aria-expanded="false" aria-label="Toggle navigation">

            <span class="navbar-toggler-icon"></span>

        </button>

        <div class="container">

            <div class="row">

                <div class="col-lg-12 text-center">

                    <p class="navbar-brand" style="margin-bottom: 2px;">Estudio sobre tráfico</p>

                </div>

            </div>

            <!--

            <div class="collapse navbar-collapse" id="navbarExample">

                <ul class="navbar-nav ml-auto">

                    <li class="nav-item active">

                        <a class="nav-link" href="#">Home <span class="sr-only">(current)</span></a>

                    </li>

                    <li class="nav-item">

                        <a class="nav-link" href="#">About</a>

                    </li>

                    <li class="nav-item">

                        <a class="nav-link" href="#">Services</a>

                    </li>

                    <li class="nav-item">

                        <a class="nav-link" href="#">Contact</a>

                    </li>

                </ul>

            </div>

            -->

        </div>

    </nav>

    <!-- Page Content -->

    <div class="container">

        <div id="intro" class="row">

            <div class="col-lg-12 text-center">

                <?php

                if (!$conn) {

                    echo "error";

                }

                else{

                    $sql_user = "INSERT INTO users (`id`, `dni`, `gender`, `job`, `subjob`, `created_at`) VALUES (NULL, '`$id`, `$dni`, `$gender`, `$job`, `$subjob`, `$created_at`)";

                }

                if (mysqli_query($conn,$sql_user) === TRUE) { //ISSUE

                    $user_id = mysqli_insert_id($conn);

                    echo '<h3 class="mt-5">Datos almacenados con éxito!</h3>';

                } else {

                    echo "Error:" . $sql_user . "<br>" . $conn->    error;

                }

                // Seleccionamos el usuario con esa id

                $sql_user_query = "SELECT * FROM users WHERE id=$user_id";

                $result_user = mysqli_query($conn, $sql_user_query); //ISSUE

                // Únicamente habrá una fila (un usuario con esa id), damos valor a las variables

                while($row = mysqli_fetch_array($result_user)) //ISSUE

                {

                    $dni_query = $row['dni'];

                    $gender_query = $row['gender'];

                    $job_query = $row['job'];

                    $subjob_query = $row['subjob'];

                    $created_at_query = $row['created_at'];

                    }

                ?>      

                <?php

                for($i = 1 ; $i <= $num_tasks ; $i++){

                    $task = $i;

                    $YES = "Sí";

                    $NO = "NO";

                    $img = $_POST["img".$i];

                    $time = $_POST["time".$i];

                    if(isset($_POST["confidence_level".$i])){

                        $confidence_level = $_POST["confidence_level".$i];

                    }

                    else{

                        $confidence_level = "";

                    }

                    if (!$conn) {

                    echo "error";

                    }

                    else{

                        $sql_data = "INSERT INTO data (`id`, `user_id`, `task`, `img`, `YES`, `NO`, `time`, `confidence_level`, `created_at`) VALUES (NULL, `$id`, `$user_id`, `$task`, `$img`, `$YES`, `$NO`, `$time`, `$confidence_level`, `$created_at`)";

                       

                        }

                    if (mysqli_query($conn,$sql_data) === TRUE) {

                        $data_id = mysqli_insert_id($conn);

                        // Seleccionamos el dato con esa id

                        $sql_data_query = "SELECT * FROM data WHERE id=$data_id";

                        $result_data = mysqli_query($conn, $sql_data_query);

                        // Únicamente habrá una fila (un usuario con esa id), damos valor a las variables

                        while($row = mysqli_fetch_array($result_data))

                        {

                            $user_id_query = $row['user_id'];

                            $task_query = $row['task'];

                            $img_query = $row['img'];

                            $Sí_query = $row['YES'];

                            $NO_query = $row['NO'];

                            $time_query = $row['time'];

                            $confidence_level_query = $row['confidence_level'];

                            $created_at_query =$row['created_at'];

                   

                        }

                    } else {

                        echo "Error:" . $sql_data . "<br>" . $conn->    error;

                    }

                    }

                       

                ?>

                                

            </div>

            <div class="col-lg-12 text-center" style="margin-top: 30px;">

                    <button style="margin-bottom: 40px" type="button" class="btn-primary selectable-element float-right" onclick="finish()">Finalizar</button>

                </div>

            </div>

        </div>

    </div>

    <script src="myjs.js"></script>

    <!-- Bootstrap core JavaScript -->

    <script src="vendor/jquery/jquery.min.js"></script>

    <script src="vendor/tether/tether.min.js"></script>

    <script src="vendor/bootstrap/js/bootstrap.min.js"></script>

</body>
#2

Why is there an extra quote before the $id?
First, do not use smart quotes, use just quotes. use ’ not ` . And, === TRUE does not check for the
results correctly. Do you understand the what == vs === is? I would use either one of these two to check for errors …
Run the query and use
if (mysqli_connect_errno()) {
to check if there was an error OR run the query and check at the same time like you did this way:
if (!mysqli_query($conn, $sql_user)) {

Either will work better than using ===

#3

Thanks for your reply,

  • quote before $id is a typo, fixed.

  • changed quotes, nothing broke (no idea why were used).

  • I did not know == vs === (as i don’t know 99% of the php operators, i have to make these scrips work and i’m learning while reading, like learning to swim while thrown in the middle of the sea)

  • i deleted line 90
    if (mysqli_query($conn,$sql_user) == TRUE) {
    and wrote
    if (mysqli_connect_errno($conn,$sql_user)) {
    is this correct?

Right now line 90 does not give any error (as badly formatted code)
but the “Could not Save User - Error” appears. So probably $conn is at fault?

if (mysqli_connect_errno($conn,$sql_user)) {

                $user_id = mysqli_insert_id($conn);

                echo '<h3 class="mt-5">Datos almacenados con éxito, puedes cerrar la ventana! Gracias por su colaboración</h3>';

            } else {

                echo "Could not Save User - Error:" . $sql_user . "<br>" . $conn->  error;

In the file “config.php” i have declared what $conn is like this:

// Servidor y credenciales para bdd

$servername = “localhost”;

$username = “xxx”; (xxx on purpose for privacy)

$password = “xxx”; (xxx on purpose for privacy)

$dbname = “estudiotraficov2”;

// Se crea la conexión

$conn = mysqli_connect($servername, $username, $password, $dbname);

#4

Well, not sure what you are trying to do. This is what you show:

if (!$conn) {
     echo "error";
} else {
    $sql_user = "INSERT INTO users (`id`, `dni`, `gender`, `job`, `subjob`, `created_at`) VALUES (NULL, '`$id`, `$dni`, `$gender`, `$job`, `$subjob`, `$created_at`)";
}
if (mysqli_query($conn,$sql_user) === TRUE) { //ISSUE
    $user_id = mysqli_insert_id($conn);
    echo '<h3 class="mt-5">Datos almacenados con éxito!</h3>';
} else {
     echo "Error:" . $sql_user . "<br>" . $conn->    error;
}
// Seleccionamos el usuario con esa id
$sql_user_query = "SELECT * FROM users WHERE id=$user_id";
$result_user = mysqli_query($conn, $sql_user_query); //ISSUE
// Únicamente habrá una fila (un usuario con esa id), damos valor a las variables
while($row = mysqli_fetch_array($result_user)) //ISSUE

So, first, you check if there is NOT a connection. But, no connection code was shown here.
If there is no connection, you throw out an error.
If there is a connection, you insert data. But, you do NOT show where this data comes from.
So, who knows if that data is accurate or not, or if it even exists to start with.
Next, you check for a user-id without even knowing if the previous function worked.?
But the query created, $sql_user is in a different { } section.
Lastly, you check for the user info without even knowing if it was created.

This entire sequence is all confused and mixed up. First, why are you attempting to create a user by inserting new data, then, using it immediately in the same code? Also, where does the info come from since you do not show that data being created anywhere. Such as $gender? Where is $gender created?
You can create a user record and use it in the same process, but, why would you do that? Makes little sense program-wise.

Perhaps you should tell us what you are trying to do. There is no form on your page to input the values you are trying to insert into your database. There is no database connection set up, therefore $conn is never created and therefore can not be used.

To input data, it needs to come from somewhere, normally from a form or other database query. Let us know what you are trying to do and we can help…

#5

The errors you are getting are follow-on errors and are not the actual problem. There would also a part of that Warning message mentioning - null given… You should be getting a bunch of php notices, starting with one about an undefined variable $conn (there’s no code in the posted code making a connection to the database server.).Start by finding the php.ini that your system is using and set php’s error_reporting to E_ALL so that php will report and display all the errors it detects. You will then get help from php in finding missing variables and logic mistakes in the code.

Next, if you are just starting out, switch to the much simpler, more consistent, and better designed PDO database extension. You should also use exceptions for database statement errors and in most cases let php catch and handle any exception, letting you remove the existing database error handling logic (one of the mistakes in the code that is causing an undefined $sql_user variable is because the connection error handling logic doesn’t surround all of the correct code and doesn’t stop execution upon a fatal database error.)

Lastly, you should reorganize all of the php logic, which will make it easier to debug the problems in the code. Your code should be laid out in this general order -

  1. Initialization - define, create, … things that the page needs.
  2. Post method form processing.
  3. Get method business logic - get/produce data needed to display the dynamic content.
  4. Html document.
#6

Again thanks for the patience and for the replies.

“the entire sequence is all confused and mixed up” - imagine how i’m feeling trying to bang my head into making sense of it, having almost zero previous experience in php. The guy who coded it probably tried his best but made things worse.

Basically we had a db and one experiment. Working. Choices, reaction times, images selected, userdata like gender, age etc etc. Things saved in the db (innoDB) and we had output.

What happened is that someone coded another experiment and made a “frankenstein”, what i found was that the output of this other experiment (lets call it E2) was saved inside database of experiment one (E1). Of course tables created for E1 were showing only partial data from E2 (images, id, age etc had the same table name so they were saved), other values that were present only in E2 would not appear (of course) in the E1 mysql db.

So, the guy who coded used bootstrap for interface (js, css etc) and some files (.php) where he copied code from E1 and tried to adapt it for E2. This is the situation: me tried to get E2 to work correctly trying to create the things that were left “dangling”.

I’ve now established the connection to the correct db (a file called “config.php” was in charge of making the connection) but was not called within test end file ("fin_prueba.php) which is launched at the end of “index.php” which is the main file (convoluted, again not my idea).

in the config.php there is

error_reporting(E_ALL);

so i already should see everything as suggested.

Now, in “fin_prueba.php” i start with by recalling config.php

The php version is 5.3 so it’s correct to use mysqli_connect_error() to check.

When i run the experiment everything goes smooth until the last page (fin_prueba.php)
which now lists many errors that are (yay progress) related to the inability to save into the db values.
At least now it connects to the db.

Error example

in_prueba.php on line 98 Could not Save User - Error:INSERT INTO users (id, dni, gender, job, subjob, created_at) VALUES (NULL, , , , , , )

The issue is now understanding where data is created from, how it’s called and successfuly store it inside the db. But i absolutely need to study php a bit more.

#7

Well, we understand what you are doing. So, you need to learn where the data is coming from first.
This would be in the forms handling area where the form is posted and the PHP code creates the
variables from the posted data. Then, you also need to check the database structure to see if the
new fields are in place in the table you are storing the data into. Once you understand these two items,
you can track down where the errors are causing it to fail. You are saving data into a table “users”.
Normally, you only save data about a user in that table and you would save their responses in another
table with a different name.

You are limited in your knowledge of PHP as you stated. You could learn about it on a beginner’s site.
Here is a good one to start with. On the top select the language. I is set at PHP now. Then, on the left
side select what you need to learn about. This will help you understand the basics. That site does not
always have the best code in it, but, is very easy to understand the way the explain things. I do think
it will help you get started. When, stuck ask back here and show us the code that is giving you problems.
PHP Forms

#8

Definition is the key to writing successful code. Without a stateable definition of what you are trying to accomplish at each step, you don’t have anything to test against to know that the code is doing what you expect and you won’t know when you are finished writing code.

You need to first define what the work-flow (steps) are, then define what data you need at each step. This lets you define what the user interface will be for each step and what data table(s) you will need.

Registering a new subject who will take a test is a separate step and should be completed before the test begins. If this is a self-administered test, the already registered subject would first login to the system. This would identify them, by storing their user id (auto-increment primary index from the users table) in a session variable. If this is a staff-administered test, the staff member would first login to the system, then select the user who is taking the test. Both the user id of the staff-member and of the user taking the test would be stored in session variables. Upon the first submission of test data, you would insert a row in a test_administered (or similarly named table), with the unique/one-time information about the test - the user id of the subject taking the test, the user id of the staff member giving the test, the datatime of the test, and any other relevant information about the instance of each test. You would then get the last insert id from this query, i.e. a test administration id, and store it in a session variable. You would then use this id when inserting each row of the test data into the data table.

Sponsor our Newsletter | Privacy Policy | Terms of Service