2

i want to insert some data in a table, and after that directly retrieve all data from that table. But strange thing is that I get an error when I do this.

So my questions is, is it possible to build a function which inserts some data via $_POST and after that directly retrieves all data from the table including the fresh new inserted $_POST data?

error:

Notice: Undefined variable: mysqli in /mnt/webr/c3/76/54476376/htdocs/includes/functions.php on line 293 Fatal error: Call to a member function prepare() on null in /mnt/webr/c3/76/54476376/htdocs/includes/functions.php on line 293

/**********************************************************************************************************************************

2. Customer new ******************************************************************************************************************

***********************************************************************************************************************************/

function customer_new($user_name) {

    //DB settings
     include_once "config/config_fl.php" ;
           $query_insert = ("INSERT INTO customers (user_name               , 
                                                    customer_name               ,
                                                                              customer_legal_sort         ,
                                                                            customer_vat_applicable ,
                                                                            customer_payment_terms  ,
                                                                            customer_contactperson  ,
                                                                            customer_email          ,
                                                                            customer_telphone       ,
                                                                            customer_address_visit  ,
                                                                            customer_number_visit   ,
                                                                            customer_num_add_visit  ,
                                                                            customer_postal_visit   ,
                                                                            customer_city_visit     ,
                                                                            customer_country_visit  ,
                                                                            customer_visit_vs_post  ,
                                                                            customer_address_post     ,
                                                                            customer_number_post      ,
                                                                            customer_num_add_post       ,
                                                                            customer_postal_post    ,
                                                                            customer_city_post      ,
                                                                            customer_country_post   ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

          $stmt = $mysqli->prepare($query_insert);
          $stmt->bind_param("sssssssssissssssissss",  $user_name                        ,
                                                                            $_POST['customer_name']           ,
                                                                            $_POST['customer_legal_sort']     ,
                                                                            $_POST['customer_vat_applicable'] ,
                                                                            $_POST['customer_payment_terms']  ,
                                                                            $_POST['customer_contactperson']  ,
                                                                            $_POST['customer_email']          ,
                                                                            $_POST['customer_telphone']       ,
                                                                            $_POST['customer_address_visit']  ,
                                                                            $_POST['customer_number_visit']   ,
                                                                            $_POST['customer_num_add_visit']  ,
                                                                            $_POST['customer_postal_visit']   ,
                                                                            $_POST['customer_city_visit']     ,
                                                                            $_POST['customer_country_visit']  ,
                                                                            $_POST['customer_visit_vs_post']  ,
                                                                            $_POST['customer_address_post']   ,
                                                                            $_POST['customer_number_post']    ,
                                                                            $_POST['customer_num_add_post']   ,
                                                                            $_POST['customer_postal_post']    ,
                                                                            $_POST['customer_city_post']      ,
                                                                            $_POST['customer_country_post']   );
          $stmt->execute();
          $stmt->store_result();
   }



/**********************************************************************************************************************************

3. Customer info ******************************************************************************************************************

***********************************************************************************************************************************/

function customer_info($user_name) {
  unset($mysqli);
  //DB settings
  include_once "config/config_fl.php" ;

  $query_select = ("SELECT * FROM customers where user_name = ? ");
  $stmt = $mysqli->prepare($query_select);
  $stmt->bind_param("s", $user_name);
  $stmt->execute();
  $stmt->store_result();
  $count = $stmt->num_rows();

   $stmt->bind_result ($customer_number         ,
                       $user_name               , 
                       $customer_name               ,
                       $customer_legal_sort       ,
                       $customer_vat_applicable ,
                       $customer_payment_terms  ,
                       $customer_contactperson  ,
                       $customer_email          ,
                       $customer_telphone       ,
                       $customer_address_visit  ,
                       $customer_number_visit   ,
                       $customer_num_add_visit  ,
                       $customer_postal_visit   ,
                       $customer_city_visit     ,
                       $customer_country_visit  ,
                       $customer_visit_vs_post  ,
                       $customer_address_post     ,
                       $customer_number_post      ,
                       $customer_num_add_post       ,
                       $customer_postal_post    ,
                       $customer_city_post      ,
                       $customer_country_post   ); 


      $stmt->fetch();

    //  $customer_data=array();


      $customer_data = array ( 'customer_number'        =>$customer_number         ,
                               'user_name'              =>$user_name               , 
                               'customer_name'          =>$customer_name                 ,
                               'customer_legal_sort'    =>$customer_legal_sort       ,
                               'customer_vat_applicable'=>$customer_vat_applicable ,
                               'customer_payment_terms '=>$customer_payment_terms    ,
                               'customer_contactperson' =>$customer_contactperson  ,
                               'customer_email'         =>$customer_email          ,
                               'customer_telphone'      =>$customer_telphone         ,
                               'customer_address_visit' =>$customer_address_visit  ,
                               'customer_number_visit'  =>$customer_number_visit     ,
                               'customer_num_add_visit' =>$customer_num_add_visit    ,
                               'customer_postal_visit'  =>$customer_postal_visit   ,
                               'customer_city_visit'    =>$customer_city_visit     ,
                               'customer_country_visit' =>$customer_country_visit    ,
                               'customer_visit_vs_post' =>$customer_visit_vs_post    ,
                               'customer_address_post'  =>$customer_address_post     ,
                               'customer_number_post'   =>$customer_number_post    ,
                               'customer_num_add_post'  =>$customer_num_add_post     ,
                               'customer_postal_post'   =>$customer_postal_post    ,
                               'customer_city_post'     =>$customer_city_post      ,
                               'customer_country_post'  =>$customer_country_post   ); 

  $stmt->close();
  $mysqli->close();
  return $customer_data; 

}
Krooy_mans
  • 304
  • 3
  • 10
  • perhaps update your question with the code you have tried and the errors you have received – Michael Doye Mar 15 '15 at 16:39
  • Just do a header redirect to the same file having queried a SELECT. – Funk Forty Niner Mar 15 '15 at 16:41
  • Fred -ii- do you mean a header RETURN from the INSERT statement function part? – Krooy_mans Mar 15 '15 at 16:42
  • On a successful insert/update, add a header after the query. Or, you can add a query select outside your functions to show existing data, while using a header on successful insert/update/delete which will then show the newly added post. – Funk Forty Niner Mar 15 '15 at 16:44
  • Doesnt worl: ation - headers already sent by (output started at (...) – Krooy_mans Mar 15 '15 at 16:48
  • You probably have an echo or HTML on top of your present code. If so, place that under your PHP/SQL, or try adding `` at the top of your pages. That sometimes works. – Funk Forty Niner Mar 15 '15 at 16:49
  • Also two functions doen`t work, then it says my MYSQLI variable isn`t set anymore at the select stamtent – Krooy_mans Mar 15 '15 at 16:49
  • @Fred-ii-, I don't get why he would need to redirect - if he has inserted the data into the table then it should be immediately available. – Peter Bowers Mar 15 '15 at 16:57
  • @Krooy_mans did you solve your error with the bind_param on line 287-8? Have you confirmed (via phpMyAdmin or otherwise) that the data actually is being inserted? – Peter Bowers Mar 15 '15 at 16:59
  • Yes, the table is updated continuosly. And strange this is that, when i just show the debiteuren.php page i get all data i want, but when i INSERT fist and then directly SELECT the SELECT statement fails – Krooy_mans Mar 15 '15 at 17:02
  • @PeterBowers I must've misunderstood the question then. That's the impression I got from reading their 2nd paragraph. – Funk Forty Niner Mar 15 '15 at 20:14

2 Answers2

1

I think the problem is that you can't execute more than one mysqli_execute without releasing the result of each of them. Maybe you should try, instead of $stmt->close() a $stmt->free_result() and see if that works. Anyway, I suggest you to always print the mysqli error to see what isn't really working.

<?php
include_once "config/config_fl.php" ;
function customer_new($user_name) {
   global $mysqli;

    $query_insert = ("INSERT INTO customers (
                        user_name               , 
                        customer_name               ,
                        customer_legal_sort         ,
                        customer_vat_applicable ,
                        customer_payment_terms  ,
                        customer_contactperson  ,
                        customer_email          ,
                        customer_telphone       ,
                        customer_address_visit  ,
                        customer_number_visit   ,
                        customer_num_add_visit  ,
                        customer_postal_visit   ,
                        customer_city_visit     ,
                        customer_country_visit  ,
                        customer_visit_vs_post  ,
                        customer_address_post     ,
                        customer_number_post      ,
                        customer_num_add_post       ,
                        customer_postal_post    ,
                        customer_city_post      ,
                        customer_country_post   
                    ) VALUES (
                        ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
                    )"
    );

    $stmt = $mysqli->prepare($query_insert);
    $stmt->bind_param("sssssssssissssssissss",  $user_name                        ,
        $_POST['customer_name']           ,
        $_POST['customer_legal_sort']     ,
        $_POST['customer_vat_applicable'] ,
        $_POST['customer_payment_terms']  ,
        $_POST['customer_contactperson']  ,
        $_POST['customer_email']          ,
        $_POST['customer_telphone']       ,
        $_POST['customer_address_visit']  ,
        $_POST['customer_number_visit']   ,
        $_POST['customer_num_add_visit']  ,
        $_POST['customer_postal_visit']   ,
        $_POST['customer_city_visit']     ,
        $_POST['customer_country_visit']  ,
        $_POST['customer_visit_vs_post']  ,
        $_POST['customer_address_post']   ,
        $_POST['customer_number_post']    ,
        $_POST['customer_num_add_post']   ,
        $_POST['customer_postal_post']    ,
        $_POST['customer_city_post']      ,
        $_POST['customer_country_post']   );
    $stmt->execute();

    // You don't need to store result
    $stmt->free_result();
}

function customer_info($user_name) {
 global $mysqli;
//No sense of unsetting mysqli variable

$query_select = ("SELECT  customer_number, customer_name, customer_legal_sort, custom_var_applicable, ... FROM customers where user_name = ? ");
$stmt = $mysqli->prepare($query_select);
$stmt->bind_param("s", $user_name);
$stmt->execute();
$stmt->store_result();
$count = $stmt->num_rows();

// You must specify what columns are you selecting to fetch them, or it won't work

$stmt->bind_result ($customer_number         ,
        $user_name               , 
        $customer_name               ,
        $customer_legal_sort       ,
        $customer_vat_applicable ,
        $customer_payment_terms  ,
        $customer_contactperson  ,
        $customer_email          ,
        $customer_telphone       ,
        $customer_address_visit  ,
        $customer_number_visit   ,
        $customer_num_add_visit  ,
        $customer_postal_visit   ,
        $customer_city_visit     ,
        $customer_country_visit  ,
        $customer_visit_vs_post  ,
        $customer_address_post     ,
        $customer_number_post      ,
        $customer_num_add_post       ,
        $customer_postal_post    ,
        $customer_city_post      ,
        $customer_country_post   ); 

while($stmt->fetch()) {
    $customer_data = array ( 
            'customer_number'        =>$customer_number         ,
            'user_name'              =>$user_name               , 
            'customer_name'          =>$customer_name                 ,
            'customer_legal_sort'    =>$customer_legal_sort       ,
            'customer_vat_applicable'=>$customer_vat_applicable ,
            'customer_payment_terms '=>$customer_payment_terms    ,
            'customer_contactperson' =>$customer_contactperson  ,
            'customer_email'         =>$customer_email          ,
            'customer_telphone'      =>$customer_telphone         ,
            'customer_address_visit' =>$customer_address_visit  ,
            'customer_number_visit'  =>$customer_number_visit     ,
            'customer_num_add_visit' =>$customer_num_add_visit    ,
            'customer_postal_visit'  =>$customer_postal_visit   ,
            'customer_city_visit'    =>$customer_city_visit     ,
            'customer_country_visit' =>$customer_country_visit    ,
            'customer_visit_vs_post' =>$customer_visit_vs_post    ,
            'customer_address_post'  =>$customer_address_post     ,
            'customer_number_post'   =>$customer_number_post    ,
            'customer_num_add_post'  =>$customer_num_add_post     ,
            'customer_postal_post'   =>$customer_postal_post    ,
            'customer_city_post'     =>$customer_city_post      ,
            'customer_country_post'  =>$customer_country_post   
    ); 
}
$stmt->free_result();
return $customer_data; 

}

You don't need to include your PHP file inside functions, you can only include it once at the beginning of the file, just remember to call the $mysqli variable as global (as you can see). After insert query you were storing result, but it doesn't make any sense, because you don't need to get any data from the SQL INSERT, maybe just a number of inserted rows or just to check if the statement has done, but, each of these 2 scopes don't need to store_result().

Another thing,in your customer_info function you're binding results from SQL SELECT which returns all columns from the Table. To use bind_result() functions, you need to specify which columuns are you going to need, so you will use a SELECT like this:

SELECT col1,col2,col3 FROM tablename WHERE col1=?

and then:

$stmt->bind_result(col1,col2,col3);

P.S: maybe you should try viewing the exactly mysqli_error which your statement is throwing, those undefined errors won't never help so much. Just one last thing, you're executing lots of mysqli functions without checking anything, like if the mysqli_execute has worked or not, or anything similar. This could help you much more, instead of using all the functions like a chain

CapitanFindus
  • 1,498
  • 15
  • 26
  • Thanks, but doesn`t work out: Notice: Undefined variable: mysqli in /mnt/webr/c3/76/54476376/htdocs/includes/functions.php on line 297 Fatal error: Call to a member function prepare() on null in /mnt/webr/c3/76/54476376/htdocs/includes/functions.php on line 297 – Krooy_mans Mar 15 '15 at 19:20
  • It`s so strange. It work when no update is triggered. But when i first insert a new row, and afterwards directly SELECT * then $MYSQLI doesn`t exist anymore. can you please have a look again? Many thanks in advance – Krooy_mans Mar 15 '15 at 19:21
  • In facts, mysqli close closes the connections, so your $mysqli variable won't be usable again. You should run that function when everything has finished – CapitanFindus Mar 15 '15 at 19:22
  • ok, I understand, but please see the original post, I now create two functions from where I left the close() function. But still $MYSQLI is gone – Krooy_mans Mar 15 '15 at 19:27
  • Thanks mate, the updated answer fixed it. The problem was related to the `Global $MYSQLI´ variable.. A very big thanks for your suppert here, and we´ve a working script here! But....what`s the deal with the 'global' thing? I don`t understand why you can`t call it twice? – Krooy_mans Mar 15 '15 at 19:54
  • You should see variable scopes in the PHP documentation, but, I can briefly explan why you must use it twice: every time you create a function, you also create its variables, which can be used only inside of it. When your variable ($mysqli) is inside another PHP file (which obviously must be included) you should refer to that by using GLOBAL – CapitanFindus Mar 15 '15 at 19:57
  • Thanks CapitanFindus! You`ve made my day! Cheers, buy yourself a beer on me! – Krooy_mans Mar 15 '15 at 20:01
1

to answer your question: yes, it is possible to first INSERT a row to a table and then retrieve it with SELECT. I think, your script fails because you close the mysql-connection after your INSERT.

A few more things to consider:

Community
  • 1
  • 1
Thorsten
  • 3,102
  • 14
  • 14