3

I have custom table like this:

useraw

1. id (Primary*)
2. user_ip
3. post_id
4. time

I am inserting data in the table using

$wpdb->insert($table_name , array('user_ip' => $user_ip, 'post_id' =>
$postID, 'time' => $visittime),array('%s','%d', '%d') );

There are four rows I inserted using this code:

id                  :        245
user_ip             :        245.346.234.22
post_id             :        24434
time                :        255464

id                  :        345
user_ip             :        245.346.234.22
post_id             :        23456
time                :        23467

id                  :        567
user_ip             :        245.346.234.22
post_id             :        57436
time                :        5678

id                  :        234
user_ip             :        245.356.134.22
post_id             :        2356
time                :        45678

I want to learn how to use MySQL queries in WordPress. So here are my questions:

  1. How to display all the data of table?
  2. How to replace data if condition matched. Like I want change the time where user_ip = 245.356.134.22

Please let me know if there is something I must need to learn.

Thank You

4

To fetch data from database table

<?php
$results = $wpdb->get_results( "SELECT * FROM $table_name"); // Query to fetch data from database table and storing in $results
if(!empty($results))                        // Checking if $results have some values or not
{    
    echo "<table width='100%' border='0'>"; // Adding <table> and <tbody> tag outside foreach loop so that it wont create again and again
    echo "<tbody>";      
    foreach($results as $row){   
    $userip = $row->user_ip;               //putting the user_ip field value in variable to use it later in update query
    echo "<tr>";                           // Adding rows of table inside foreach loop
    echo "<th>ID</th>" . "<td>" . $row->id . "</td>";
    echo "</tr>";
    echo "<td colspan='2'><hr size='1'></td>";
    echo "<tr>";        
    echo "<th>User IP</th>" . "<td>" . $row->user_ip . "</td>";   //fetching data from user_ip field
    echo "</tr>";
    echo "<td colspan='2'><hr size='1'></td>";
    echo "<tr>";        
    echo "<th>Post ID</th>" . "<td>" . $row->post_id . "</td>";
    echo "</tr>";
    echo "<td colspan='2'><hr size='1'></td>";
    echo "<tr>";        
    echo "<th>Time</th>" . "<td>" . $row->time . "</td>";
    echo "</tr>";
    echo "<td colspan='2'><hr size='1'></td>";
    }
    echo "</tbody>";
    echo "</table>"; 

}
?>

NOTE: Change your data fetching format according to your need (table structure)

To update time field on if condition

<?php if($userip==245.356.134.22){  //Checking if user_ip field have following value
$wpdb->update( 
$table_name, 
array( 
    'time' => 'YOUR NEW TIME' // Entring the new value for time field
),      
array('%d')                   // Specify the datatype of time field
);
}
?>

Update

If you want to check if the IP you are going to insert in database is already exist or not then check it like this

<?php
global $wpdb,$ip;
$results = $wpdb->get_results( "SELECT user_ip FROM $table_name");  //query to fetch record only from user_ip field

$new_ip = 245.356.134.22;   //New Ip address storing in variable

if(!empty($results))                       
{    
    foreach($results as $row){  
    $old_ip = $row->user_ip;        // putting the value of user_ip field in variable
    if($new_ip==$old_ip){           //  comparing new ip address with old ip addresses
      $ip = 'Already Exist';        // if ip already exist in database then assigning some string to variable
    }
    }

}
if($ip = 'Already Exist'){          // Checking if variable have some string (It has some string only when if IP already exist in database as checked in if condition by comparing old ips with new ip)
//Insert query according to Ip already exist in database
}else{
//Insert query according to Ip doesn't exist in database
}


?>
  • That's great... can you tell me how I can check if the ip address is already in database or not? If Ip address already in database then run program 1 and if it's not, then run program 2. I know we need to use WHERE in query but I dont know hot to use it – Ramesh Pardhi Jul 25 '16 at 8:16
  • Have you seen the updated answer? – Rishabh Jul 26 '16 at 5:32
  • I tried this and its working. But right now its fetching all the data from database using foreach, then its compiling our code to check if the ip is same or not. Is there any other way to get that ip get checked directly using database query? – Ramesh Pardhi Jul 26 '16 at 12:04
  • Answer has been updated! Check if its what you need. – Rishabh Jul 27 '16 at 5:18
  • 1
    Yes you can use it like this` $results = $wpdb->get_results( "SELECT user_ip FROM $table_name WHERE user_ip = '$username' ");` – Rishabh Jul 27 '16 at 8:57
1
//For fetching data use
global $wpdb;
$results = $wpdb->get_results("SLECT * FROM table_name"); 
//and for update use below code 
$wpdb->update( 
  $table_name, 
  array( 
    'time' => time(),   // string
  ), 
  array( 'user_ip' => '245.356.134.22' ), 
  array('%s'), 
  array( '%d' )
);
  • Please add some comments in front of you code lines so that viewer can understand what you code does and how. – Rishabh Jul 27 '16 at 6:01
  • @Rishabh no need for more comment as i have inserted appropriate comment before the code. – Ganesh Jul 27 '16 at 6:36
0

You indicate MYSQLi in your question, but label and refer to MySQL in your question. If you are using MySQL the following will work for you:

This is fairly simple, and given the fact that you already have the insert statement constructed, you can just use update like so:

$wpdb->update($table_name , array('user_ip' => $user_ip, 'post_id' =>$postID, 'time' => $visittime),array('%s','%d', '%d') );

Then all you would have to do is set the values in your query as to what data in the DB you want to change. You can find examples here: https://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows

As for fetching all of the data you could use the follow:

$results = $wpdb->get_results("SLECT * FROM table_name"); 

You can add any WHERE parameters to it or sort it just like and standard SQL query statement. You can just then loop through is with a foreach loop and then you would just echo out your data you retrieved.

  • Thank you for reply @hosker. But I was wanted to know hot to fetch that $results – Ramesh Pardhi Jul 25 '16 at 8:18
  • 1
    I am willing to help, unlike @rishabh, who was nice enough to give you a complete answer with coding, I was just giving you the basics of how to do what you were asking for. A simple google search and a look through the Wordpress Codex could have solved your problem. These forums are here as a resource, and I use them, but I would never expect someone to code it for me. You had half of it already set, but were too lazy to figure it out and write the code yourself. You never learn if someone does it all for you. – hosker Jul 25 '16 at 23:16
  • @ hosker you are right buddy! Self study is must... Now I realize I shouldn't have given the complete answer. – Rishabh Jul 26 '16 at 5:36
  • Hmm! you are right guys. what I asked here is for my reference. What I am trying to do is something different. I want to know some things to use it with my plugin. What I want to know is how to use these queries to show the IP I want without fetching all the data from database. I want a query which checks for the IP I want and then displays it. What if I have 30000 IPs? It will fetch all the 30000 IPs using foreach. That's not right! Thats why I need your help. – Ramesh Pardhi Jul 26 '16 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.