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:
- How to display all the data of table?
- How to replace data if condition matched. Like I want change the
time
whereuser_ip = 245.356.134.22
Please let me know if there is something I must need to learn.
Thank You
3 s
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
}
?>