I have two tables in database called tbl_rotator_urls and tbl_rotators_data
tbl_rotator_urls contains urls and tbl_rotators_data contains urls visit history with ip address.
I am redirecting users based on url position. on first visit user getting redirected on first position url, on second visit user getting redirected on second position url and so on.
for above thing, I am checking table like this and making query like below
$visited_urls=-1;
$stmt = $conn->prepare("SELECT link_id
FROM tbl_rotators_data
WHERE rotator_id = ?
AND link_id !=0
AND ip=?");
$stmt->bind_param('is',$rotator_id,$ip);
$stmt->execute();
$visited_result = $stmt->get_result();
$total_visited_urls = $visited_result->num_rows;
$stmt->close();
$visited_array = array();
if($total_visited_urls>0){
while($visited_row=mysqli_fetch_array($visited_result)){
$visited_array[] = $visited_row['link_id'];
}
if(sizeof($visited_array)>0){
$visited_urls = implode(',', $visited_array);
}else{
$visited_urls=-1;
}
}
so its giving me comma seperated list of users visited url
which I am using like below for get next remain url
//select URL which is not visited by USER
$stmt = $conn->prepare("SELECT u.url_id,u.url_url,
FROM tbl_rotator_urls u
LEFT JOIN tbl_rotators_data l ON u.url_id=l.link_id
WHERE u.rotator_id = ?
AND u.url_id NOT IN($visited_urls)
AND url_status=0
GROUP BY u.url_id
ORDER BY $orderby ASC");
$stmt->bind_param('i',$rotator_id);
$stmt->execute();
$url_result = $stmt->get_result();
$total_urls = $url_result->num_rows;
if($total_urls== 0){
// USER have visited all URLS SO set $visited_urls=-1; SO it can get URL again
$visited_urls=-1; //this is doubtful point, I should do something else or more with this but no idea
$stmt = $conn->prepare("SELECT u.url_id,u.url_url,
FROM tbl_rotator_urls u
LEFT JOIN tbl_rotators_data l ON u.url_id=l.link_id
WHERE u.rotator_id = ?
AND u.url_id NOT IN($visited_urls)
AND url_status=0
GROUP BY u.url_id
ORDER BY $orderby ASC");
$stmt->bind_param('i',$rotator_id);
$stmt->execute();
$url_result = $stmt->get_result();
$total_urls = $url_result->num_rows;
}
..... CONTINUE MY CODE FOR REDIRECT
in above function if user have visited all urls, I am setting $visited_urls=-1 so I am able to get URL, but on all visit, I am getting same URL, instead I want one by one like below
for example I have 5 urls, I want repeat like
1
2
3
4
5
1
2
3
4
5
Database Table information
tbl_rotator_urls structure like below
url_id, url_name, url_url
tbl_rotators_data structure like below
rid, link_id,rotator_id, ip,timestamp
I have no idea how I can put sample data here, so I have written in excel and put image here SAMPLE DATA IMAGE
I am learning PHP and struggling from last two hours for achieve my goal but not getting idea how I can do it. Let me know if any expert here can help me for same.
Thanks!