No database selected when using INSERT in MySQL?

Question

Okay I'm quite new to php and mysql and I can't seem to figure this out. So what I'm trying to do is send some variables to a MySQL table. I have set up the connection inside a function (I believe this is possible(?) )
And with my current code it gives me the following error.

Notice: No database selected in INSERT INTO retrieveddata (profileId, profileName, pageViews, profileRevenue, currentDate) VALUES(NULL,'www.mywebsite.nl[testview2]', '48702', '0.0',NULL) in C:\xampp\htdocs\analyticsoverview\HelloAnalyticsApi.php on line 281

I have tried adding the select_db to my code but it doesn't work. This is my code.

    function printResults(&$results) {

        if (count($results->getRows()) > 0) {
        $profileName = $results->getProfileInfo()->getProfileName();
        $rows = $results->getRows();
        $sessions = $rows[0][0];
        $pageviews = $rows[0][1];
        $bounces = $rows [0][2];
        $betaalgoal = $rows [0][3];
        $transrevenue = $rows [0][4];


// set up database connection


    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "googleanalytics";

// Create connection

    $conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }    

// select database that needs to be filled with the data from the variables

    $conn->select_db('googleanalytics');

// the mysql query to insert the variables into table called retrieveddata
<
    $query    = "INSERT INTO retrieveddata (profileId, profileName, pageViews, profileRevenue, currentDate) 
         VALUES(NULL,'$profileName', '$pageviews', '$transrevenue',NULL)";
    mysql_query($query) or trigger_error(mysql_error()." in ".$query);

You already selected the DB in `$conn = new mysqli($servername, $username, $password, $dbname);` so why use `$conn->select_db('googleanalytics');`? Plus, you're mixing MySQL functions using `mysql_*`, so your entire code is failing you, *twice*. — Funk Forty Niner, May 21 '15 at 17:20
@Fred ii. Correct and please go through what he suggested to you. thumbs up — Alive to die - Anant, May 21 '15 at 17:22
@anantkumarsingh I should have just posted that as an answer, however sometimes they come back and say: *"It still doesn't work"* - You know the drill ;-) — Funk Forty Niner, May 21 '15 at 17:23
ha ha ha . yes i understand that.:):) happens often to you. isn't it? — Alive to die - Anant, May 21 '15 at 17:23
@anantkumarsingh Not to mention 281 lines; *wow*. yeah, I'll pass on this one. OP's got enough now to test it out. — Funk Forty Niner, May 21 '15 at 17:24
Yup. you are right. but sometime what i feel that your language goes beyond OP's thinking. that's why they didn't get your suggestions. it's my personal feeling. But you are correct always as much as i saw your comments and answers. — Alive to die - Anant, May 21 '15 at 17:27
thanks for the fast replies! will test some stuff out now :) — ampyfibe, May 21 '15 at 17:35
@anantkumarsingh I changed my mind, since someone posted an answer and still mixing MySQL APIs. — Funk Forty Niner, May 21 '15 at 17:48
@ampyfibe consult my answer below, if you haven't already. Unless you wish to stay on with the other person's answer, the choice is yours. — Funk Forty Niner, May 21 '15 at 17:48
@fred -ii- Thanks for your reply and answer fred I will test this out. — ampyfibe, May 21 '15 at 17:54

score 2 · Accepted Answer · edited May 23 '17 at 11:51

Firstly, you don't need $conn->select_db('googleanalytics');

You already chose your database in:

$conn = new mysqli($servername, $username, $password, $dbname);

Then, you're mixing MySQL APIs with mysql_ functions that do not intermix with any other functions.

$query = "INSERT INTO retrieveddata 
          (profileId, profileName, pageViews, profileRevenue, currentDate) 
         VALUES (NULL,'$profileName', '$pageviews', '$transrevenue',NULL)";

$result = mysqli_query($conn, $query);

if ( !$result ) {
trigger_error('query failed', E_USER_ERROR);
}

Pass the database connection to the query and for the error trigger which I changed.

Or simply, or die(mysqli_error($conn)) to mysqli_query().

$result = mysqli_query($conn, $query) or die(mysqli_error($conn));

Sidenote:

Now, if your data contains apostrophes or other data the MySQL may complain about, you will need to sanitize your data using mysqli_real_escape_string(). Otherwise, you may get syntax errors.

http://php.net/manual/en/mysqli.real-escape-string.php

I.e. and as an example:

$profileName = mysqli_real_escape_string($conn, $profileName);

and doing the same for the other variables.

Your present code is open to SQL injection. Use mysqli_ with prepared statements, or PDO with prepared statements, they're much safer.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

Currently gives me Fatal error: query failed in C:\xampp\htdocs\analyticsoverview\HelloAnalyticsApi.php on line 289. But I'll still have some other options to try. I'll let you know how it goes ;). — ampyfibe, May 21 '15 at 18:29
@ampyfibe You'll need to find out exactly what the real error is. Use `$result = mysqli_query($conn, $query) or die(mysqli_error($conn));` if you are not doing that already and check your logs. Just a `query failed` isn't enough to find out why the query failed. — Funk Forty Niner, May 21 '15 at 18:33
@ampyfibe also add error reporting, as stated near the bottom of my answer. See if that also helps. — Funk Forty Niner, May 21 '15 at 18:41

Marco · Answer 2 · 2015-05-21T18:01:07.387

0

You could delete

$conn->select_db('googleanalytics');

because when you create the connection you've already selected the database, it's the last parameter of mysqli().

Try to change:

mysql_query($query) or trigger_error(mysql_error()." in ".$query);

whith this:

$conn->query($query) or trigger_error(mysql_error()." in ".$query);

When you build your query you have to concatenate more strings, elsewhere your variables won't be correctly used

$query="INSERT INTO retrieveddata (profileId, profileName, pageViews, profileRevenue, currentDate) VALUES(NULL,'".$profileName."', '".$pageviews."', '".$transrevenue."',NULL)";

edited May 21 '15 at 18:01

answered May 21 '15 at 17:28

Marco

705
8
28

thanks for your reply, I have done what you said now it gives me Parse error: syntax error, unexpected '->' (T_OBJECT_OPERATOR) in C:\xampp\htdocs\analyticsoverview\HelloAnalyticsApi.php on line 284 – ampyfibe May 21 '15 at 17:40
`or trigger_error(mysql_error()` are you sure about that? – Sean May 21 '15 at 17:40
@zanna line 284: mysqli->query($query) or trigger_error(mysql_error()." in ".$query); – ampyfibe May 21 '15 at 17:41
Sorry I've forgotten a thing, the correct line is $conn->query($query) or trigger_error(mysql_error()." in ".$query); – Marco May 21 '15 at 17:41
Replaced it with the correct line and currently gives me: Notice: in INSERT INTO retrieveddata (profileId, profileName, pageViews, profileOmzet, currentDate) VALUES(NULL,'www.mywebsite.nl[testview2]', '48702', '0.0',NULL) in C:\xampp\htdocs\analyticsoverview\HelloAnalyticsApi.php on line 284 – ampyfibe May 21 '15 at 17:47
I've appended the correction of the query building, times ago I've got the same error and I solved it in that way – Marco May 21 '15 at 18:02

No database selected when using INSERT in MySQL?

2 Answers2