|
|
Hello,
I got this array:
````
$test = array('id','date_and_time','kw_1','kw_1_point','kw_2','kw_2_point','kw_3','kw_3_point','kw_4','kw_4_point');
````
Now how to echo all values that does not conatin '_point'.
|
|
|
|
|
|
<pre>Hiya,
I need to learn the SQL query that counts all the points from more than one column and orders the matching rows based on most points in descending order.
EXAMPLE 1:
I do a keyword search for "mobile phone tutorial apps". Note 4 words.
Sql should find all the rows that contain any of these 4 keywords.
That means, since I searched for 4 keywords, sql should even pull-up those rows that just have even 1 or 2 or 3 of these 4 keywords in them.
Say, my mysql table looks like this:
id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
--------------------------------------------------------------------------
0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2
--------------------------------------------------------------------------
1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3
--------------------------------------------------------------------------
2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3
-------------------------------------------------------------------------
3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5
NOTE: All 4 keywords exists on the first 3 matching rows. However, only 3 words exist in the final matching row.
And the final matching row's keywords are not in the order of my keyword search.
Here in Q1, this should not matter. Sql should ignore in which order the keywords are in each column when comparing the order of my searched keywords. All it should do, is:
A). Find matching rows, regardless of how many of my searched keywords exist on each row;
B). Count the totals of each points, (count more than one column in this case), in each row; And
C) List the rows in the point's descending order.
As you can see, from my example, the following are the keyword points of each row after calculating ALL their keyword points (all point columns):
id 0 = 10 point
id 1 = 8 point
id 2 = 11 point
id 3 = 20 point
So, in this case, the SQL query should present rows in this descending order:
id 3 = 20
id 2 = 11 points
id 0 = 10 points
id 1 = 8 points
So, in this case, the SQL query should present rows in this descending order:
id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
----------------------------------------------------------------------------------------------
3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5
----------------------------------------------------------------------------------------------
2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3
----------------------------------------------------------------------------------------------
0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2
----------------------------------------------------------------------------------------------
1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3
----------------------------------------------------------------------------------------------
Had there been only a single "keyword point" (kw1_point) to calculate, then I would have written the SQL like this using prepared statements:
````
$sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? order by kw1_point desc LIMIT 100";
````
kw stands for "keyword_1". So, it is a column.
"kw_1_point" is another column.
"kw_2" is another column.
"kw_2_point" is another column.
"kw_3" is another column.
"kw_3_point" is another column.
"kw_4" is another column.
"kw_4_point" is another column.
</pre>
-- modified 9-Apr-23 16:34pm.
|
|
|
|
|
I have no idea why this forum says (includes) 'MySQL', but there is a different database specific forum which this post would be better in.
|
|
|
|
|
Oh! Which place ? If you moderator then why you not transfer it there then ?
|
|
|
|
|
|
Only few Errors exists in two PHP files before Upgrading this code to PHP 8.1
Please suggest me solutions, This will fix the whole script
Final error_log:
[08-Apr-2023 15:57:03 UTC] PHP Warning: Undefined array key "m" in message.php on line 88
[08-Apr-2023 15:57:03 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND receiver_id=36376) OR (sender_id =36376 AND receiver_id=) ORDER BY `date_...' at line 1 in message.php:89
Stack trace:
#0 message.php(101): mysqli_query(Object(mysqli), 'SELECT * FROM p...')
#1 {main}
thrown in message.php on line 89
[08-Apr-2023 15:57:25 UTC] PHP Warning: Undefined array key "id" in inbox.php on line 13
[08-Apr-2023 15:57:25 UTC] PHP Warning: Cannot modify header information - headers already sent by (output started at inbox.php:13) in inbox.php on line 13
message.php
Code line 88 and 89:
$query = "SELECT * FROM ".C_MYSQL_MESSAGES." WHERE (sender_id =".$_SESSION['m']." AND receiver_id=". (int)$_GET['id'].") OR (sender_id =".(int)$_GET['id']." AND receiver_id=".$_SESSION['m'].") ORDER BY `date_added` ASC";
$result = mysqli_query($conn,$query) or die();
Complete code of message.php
<?php
@session_start();
$error = "";
include_once 'include/config.inc.php';
include_once 'include/options.inc.php';
include_once 'include/security.inc.php';
include_once 'include/functions.inc.php';
$from_name = "";
$from_id ="";
if(isset($_GET['id']) && $_GET['id'] != "")
{
$receiver_id = (int)$_GET['id'];
$tmp=mysqli_query($conn,"SELECT id,fname,lname FROM ".C_MYSQL_MEMBERS." WHERE id=".$receiver_id." AND status >= '7'");
$count=mysqli_num_rows($tmp);
$row=mysqli_fetch_array($tmp);
$from_name=$row['fname'].' '.$row['lname'];
$from_id = $row['id'];
if($count == '0') {
$error = 1;
}
}
else
{
$error = 1;
}
if($error == 1)
{
header('location: '.C_URL.'/inbox.php');
die();
}
if(!isset($_SESSION['m']) || $_SESSION['m'] == '')
{
header('location: '.C_URL.'/login.php?redirect_url='.C_URL.'/message.php?id='. (int)$_GET['id']);
}
include_once 'templates/'.C_TEMP.'/config.php';
include_once 'templates/'.C_TEMP.'/header.php';
?>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<style>
p.msgtext{
width: 80%;
clear: both;
padding: 10px 5px;
border-radius: 5px;
}
.left{
background-color: #edf9f9;
float: left;
text-align: left;
}
.right{
background-color: #f3f3f3;
float: right;
text-align: right;
}
.date_left
{
float: left;
font-size: 11px;
padding: 14px 1px 0px;
color: #adadad;
}
.date_right
{
float: right;
font-size: 11px;
padding: 14px 1px 0px;
color: #adadad;
}
</style>
<br>
<div class="row">
<br/><br/>
</div>
<div class="col-md-12" style="padding-top: 10px;">
<div class="panel panel-default">
<div class="panel-heading" style=" background-color: <?php echo COLORH ?>"><?php echo $from_name ?> [<a href="<?php echo C_URL ?>/view.php?l=default&id=<?php echo $from_id ?>"><?php echo $from_id ?></a>]</div>
<div class="panel-body" style=" background-color: <?php echo COLOR1 ?>">
<div id="messages">
<?php
if(isset($_GET['id']) && $_GET['id'] != "")
{
$query = "SELECT * FROM ".C_MYSQL_MESSAGES." WHERE (sender_id =".$_SESSION['m']." AND receiver_id=". (int)$_GET['id'].") OR (sender_id =".(int)$_GET['id']." AND receiver_id=".$_SESSION['m'].") ORDER BY `date_added` ASC";
$result = mysqli_query($conn,$query) or die();
while($j = mysqli_fetch_array($result))
{
$c = mysqli_query($conn,"UPDATE ".C_MYSQL_MESSAGES." SET status=1 WHERE id=".$j['id']);
$class = "";
$date_class = "";
if($j['sender_id'] == $_SESSION['m'])
{
$class = "right";
$date_class = "date_left";
}
else
{
$class = "left";
$date_class = "date_right";
}
echo '<p class="msgtext '.$class.'">'.$j['date_added'].''.$j['message'].'</p>';
}
}
?>
</div>
<div class="col-md-6" style="float: none;margin: auto;">
<form method="post" onsubmit="return false;" id="send_message" action="send_message.php">
<input type="hidden" name="sender_id" value="<?php if(isset($_SESSION['m']))echo $_SESSION['m'] ?>" />
<input type="hidden" name="receiver_id" value="<?php echo $receiver_id ?>" />
<textarea class="form-control" id="message_box" name="message"></textarea>
<br/>
<a class="btn btn-success" style="float:right;color: white" id="send_message_btn" href="javascript:;">Send</a>
</form>
</div>
</div>
</div>
<!--
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script>
$('#send_message_btn').click(function(){
var url = "send_message.php";
$.ajax({
type: "POST",
url: url,
data: $("#send_message").serialize(),
success: function(data)
{
if(data.trim() == '1')
{
$('#message_box').val('');
}
}
});
});
function load_messages()
{
$.ajax({
type: "POST",
url: "load_messages.php",
data: {id: <?php echo $receiver_id ?>},
success: function(data)
{
if(data.trim() != '')
{
$('#messages').append(data);
}
}
});
}
setInterval(function(){
load_messages();
}, 3000);
</script>
<?php include_once 'templates/'.C_TEMP.'/footer.php'; ?>
inbox.php
Code line 13:
header('location: '.C_URL.'/login.php?redirect_url='.C_URL.'/inbox.php'.$_GET['id']);
Complete code of inbox.php
<?php
@session_start();
$error = "";
include_once 'include/config.inc.php';
include_once 'include/options.inc.php';
include_once 'include/security.inc.php';
include_once 'include/functions.inc.php';
include_once 'templates/'.C_TEMP.'/config.php';
if(!isset($_SESSION['m']) || $_SESSION['m'] == '')
{
header('location: '.C_URL.'/login.php?redirect_url='.C_URL.'/inbox.php'.$_GET['id']);
exit;
}
include_once 'templates/'.C_TEMP.'/header.php';
?>
<?php
$unsubscribe_token = '';
$user = [];
$tmp=mysqli_query($conn,"SELECT id, purposes, gender, country, email, email_subscription FROM ".C_MYSQL_MEMBERS." WHERE id='".$_SESSION['m']."' AND status >= '7'");
if($tmp) {
$user=mysqli_fetch_array($tmp);
$unsubscribe_token = $user['id'].'-'.$user['gender'].'-'.$user['purposes'].'-'.$user['country'];
}
?>
<!--
<style>
p.msgtext{
clear: both;
padding: 10px 5px;
margin-bottom: 2px;
cursor: pointer;
background-color: #f3f3f3;
}
.name_class{
float: left;
font-weight: 600;
color: #131312;
margin-right: 20px;
}
.date_class
{
float: right;
font-size: 12px;
color: #adadad;
}
</style>
<br>
<div id="messages" class="col-md-12" style="padding-top: 10px;">
<div class="panel panel-default">
<div class="panel-heading" style=" background-color: <?php echo COLORH ?>"><?php echo $w[585] ?></div>
<div class="panel-body" style=" background-color: <?php echo COLOR1 ?>">
<?php
if($conn == null) {
$conn = mysqli_connect(C_HOST,C_USER,C_PASS,C_BASE) or die($w[113]);
}
$query = "SELECT pro_messages.*,sender.fname as sender_fname,receiver.fname as receiver_fname,sender.lname as sender_lname,receiver.lname as receiver_lname FROM ".C_MYSQL_MESSAGES." INNER JOIN ".C_MYSQL_MEMBERS." sender ON sender.id=pro_messages.sender_id INNER JOIN ".C_MYSQL_MEMBERS." receiver ON receiver.id=pro_messages.receiver_id WHERE sender_id =".$_SESSION['m']." OR receiver_id=".$_SESSION['m']." ORDER BY `date_added` desc";
$result = mysqli_query($conn,$query);
$temp = array();
$num = 1;
while($j = mysqli_fetch_array($result))
{
$name = "";
$id="";
if($j['receiver_id'] == $_SESSION['m'])
{
if(!in_array($j['sender_id'], $temp))
{
$temp[] = $j['sender_id'];
}
else
continue;
$id = $j['sender_id'];
$name = $j['sender_fname'].' '. $j['sender_lname'];
}
elseif($j['sender_id'] == $_SESSION['m'])
{
if(!in_array($j['receiver_id'], $temp))
{
$temp[] = $j['receiver_id'];
}
else
continue;
$id = $j['receiver_id'];
$name = $j['receiver_fname'].' '. $j['receiver_lname'];
}
echo '<p class="msgtext"> <a style="color: black;font-size: 16px;" href="message.php?id='.$id.'">'.$num.') '.$name.''.$j['message'].'</a>'.$j['date_added'].'</p>';
$num++;
}
if(!empty($user)) {
$action = $user['email_subscription'] == 1 ? 'unsubscribe': 'subscribe';
?>
<a href="<?php echo C_URL ?>/email_subscription.php?token=<?php echo $unsubscribe_token ?>&action=<?php echo $action ?>"><?php echo $user['email_subscription'] == 1 ? 'Unsubscribe': 'Subscribe' ?> to Email Updates</a>
<?php } ?>
</div>
</div>
<?php include_once 'templates/'.C_TEMP.'/footer.php'; ?>
|
|
|
|
|
Previously with help of this forum I was able to fix Undefined variable errors using if(isset()) for PHP 8.1.
But this time I was unable to use if(isset()) to fix this error_log:
PHP Warning: Undefined variable $sendid in /home/____/check.php on line 70
Code line 70 is
printm($str.$sendid);
As above line connected with an array, how can I use isset? Please suggest me a solution.
Full code is given below:
<?php
include_once 'include/config.inc.php';
include_once 'include/options.inc.php';
include_once 'include/functions.inc.php';
include_once 'include/security.inc.php';
include_once 'templates/'.C_TEMP.'/config.php';
include_once 'templates/'.C_TEMP.'/header.php';
if(!isset($id)||!isset($code)||!is_numeric($id)||!preg_match('/^[0-9A-Za-z]/',$code)) {
printm($w[1].'3');
}
$temp = mysqli_query($conn,"SELECT id FROM ".C_MYSQL_TEMP." WHERE date < DATE_SUB(NOW(), INTERVAL ".C_REG_DAYS." DAY)");
while ($i = mysqli_fetch_array($temp))
{
$tmp = mysqli_query($conn,"SELECT pic1, pic2, pic3 FROM ".C_MYSQL_MEMBERS." WHERE id='".$i['id']."' and status = '0'");
while ($j = mysqli_fetch_array($tmp))
{
for($k = 1; $k <= 3; $k++)
{
$tmpm = 'pic'.$k;
if (($j[$tmpm] != '') && (is_file(C_PATH.'/members/uploads/'.$j[$tmpm])))
{
@unlink (C_PATH.'/members/uploads/'.$j[$tmpm]);
}
}
@mysqli_query($conn,"DELETE FROM ".C_MYSQL_MEMBERS." WHERE id='".$i['id']."' and status = '0'");
}
@mysqli_query($conn,"DELETE FROM ".C_MYSQL_TEMP." WHERE id='".$i['id']."'");
}
$temp=mysqli_query($conn,"SELECT count(*) as total FROM ".C_MYSQL_TEMP." WHERE id='".$id."' AND code='".$code."' AND date > DATE_SUB(NOW(), INTERVAL ".C_REG_DAYS." DAY)");
$row=mysqli_fetch_array($temp);
$count = $row['total'];
if($count != '0') {
mysqli_query($conn,"DELETE FROM ".C_MYSQL_TEMP." WHERE id='".$id."' AND code='".$code."'");
if(C_CHECK_REGISTER == '3') {
$status='1';
$str=$w[159];
}
else {
$status='7';
$str = $w[46];
}
mysqli_query($conn,"UPDATE ".C_MYSQL_MEMBERS." SET status='".$status."' WHERE id='".$id."'");
$result = mysqli_query($conn,'SELECT email, password FROM '.C_MYSQL_MEMBERS.' WHERE id = \''.$id.'\'');
while($i=mysqli_fetch_array($result) && $status == "7") {
$tm=array(C_SNAME);
$subject=template($w[195],$tm);
switch (C_ID) {
case '2':
$sendid=$i['email'];
break;
default:
$sendid=$id;
break;
}
$tm=array($sendid,'____',C_SNAME);
$message=template($w[588],$tm);
sendmail(C_FROMM,$i['email'],$subject,$message,'text');
}
printm($str.$sendid);
}
else {
unset($id);
printm($w[161]);
}
include_once 'templates/'.C_TEMP.'/footer.php';
?>
|
|
|
|
|
The variable $sendid is not defined anywhere at module level, it is local to the following switch block:
switch (C_ID) {
case '2':
$sendid=$i['email'];
break;
default:
$sendid=$id;
break;
}
So once that block ends the variable no longer exists. Change it to something like:
$sendid = '';
switch (C_ID) {
global $sendid;
case '2':
$sendid=$i['email'];
break;
default:
$sendid=$id;
break;
}
You may like to review PHP: Variable scope - Manual[^].
|
|
|
|
|
I tried using your code suggestion but when user recieved verification email and clicked on verify link, it gives "HTTP ERROR 500".
Also error_log has following:
PHP Parse error: syntax error, unexpected 'global' (T_GLOBAL), expecting case (T_CASE) or default (T_DEFAULT) or '}'
|
|
|
|
|
HTTP status 500 is an internal server error. So you need to look at your server logs, and maybe even add some debug code to find out what is going wrong.
Is this your code or did someone else write it?
|
|
|
|
|
Actually this code was written by an outside person.
I will check this further.
|
|
|
|
|
Actually issue has been fixed after adding your code suggestion to another place in the same PHP file:
$tm=array($sendid,'____',C_SNAME);
$message=template($w[588],$tm);
sendmail(C_FROMM,$i['email'],$subject,$message,'text');
}
$sendid = '';
global $sendid;
printm($str.$sendid);
}
|
|
|
|
|
Udaya Arunakantha wrote: this code was written by an outside person.
Then you have my sympathy. I have faced similar issues in the past and it is never easy.
|
|
|
|
|
You are correct Richard, it's bit hard but your code suggestion worked for another code line in the same PHP code, see my last reply.
|
|
|
|
|
Following code is working fine up to PHP 7.4 but with PHP 8.1 it makes an error_log:
PHP Warning: Undefined variable $conn
Please suggest me a solution for this issue.
The relevant code line is:
if(!$conn) $conn = mysqli_connect(C_HOST,C_USER,C_PASS,C_BASE) or die($w[113]);
Full code of the function is:
function all_online_users()
{
if(!$conn) $conn = mysqli_connect(C_HOST,C_USER,C_PASS,C_BASE) or die($w[113]);
$sec = 300;
if(isset($_SESSION['m']))
{
$user = is_numeric($_SESSION['m']) ? $_SESSION['m'] : 0;
$MyAccess = 0;
}
else
{
$user = 0;
$MyAccess = -1;
}
$ip = ($user == 0) ? ip2int(ip()) : 0;
mysqli_query($conn,"DELETE FROM ".C_MYSQL_ONLINE_USERS." WHERE (time < DATE_SUB(NOW(), INTERVAL ".$sec." SECOND) AND NOW() > ".$sec.") or time > NOW()");
$result = @mysqli_query($conn,"SELECT * FROM ".C_MYSQL_ONLINE_USERS);
$cnt=array();$adduser=1;$addguest=1;$users=0;
while($result && $i = mysqli_fetch_array($result))
{
$cnt[$i['usertype']] = (isset($cnt[$i['usertype']])) ? $cnt[$i['usertype']] + 1 : 1;
If(($MyAccess >= 0) && ($user == $i['user']))
{
$adduser=$addguest=0;
}
If(($MyAccess < 0) && ($ip == $i['ip']))
{
$adduser=$addguest=0;
}
}
if($adduser || $addguest)
{
mysqli_query($conn,"INSERT INTO ".C_MYSQL_ONLINE_USERS." VALUES (NOW(),".$user.",'".$MyAccess."','".$ip."')");
$cnt[$MyAccess] = (isset($cnt[$MyAccess])) ? $cnt[$MyAccess] + 1 : 1;
}
$k=0;while(isset($cnt[$k]))
{
$users = $cnt[$k] + $users;
$k++;
}
if(isset($cnt['-1'])) define('ONLINE_GUESTS',$cnt['-1']);
else define('ONLINE_GUESTS','0');
define('ONLINE_USERS',$users);
}
all_online_users();
|
|
|
|
|
|
|
I'm testing my old PHP code with PHP 8.1 (code is working fine with PHP 7.4).
Following line is not working on PHP 8.1
$total += count($row[0]);
I tried with strlen but it gives totally wrong result because this count is counting messages, size of inbox.
if (!defined("_COMMON_")) {echo "stop";exit;}
include_once($ld_engine_path."inc_connect.php");
$m_result = mysqli_query($conn,"select new_mails from ".$mysql_table_prefix."users where id=$is_regist") or die("database error<br>".mysqli_error($conn));
if (mysqli_num_rows($m_result))
list($new_board_messages) = mysqli_fetch_array($m_result, MYSQLI_NUM);
if (!isset($new_board_messages)) $new_board_messages = "0";
mysqli_free_result($m_result);
$m_result = mysqli_query($conn,"select concat(subject,body) from ".$mysql_table_prefix."board where user_id=$is_regist") or die("database error: cannot retrieve mail-messages<br>".mysqli_error($conn));
$total = 0;
while ($row = mysqli_fetch_array($m_result, MYSQLI_NUM))
$total += count($row[0]);
$percentage = round($total / $max_mailbox_size *100);
mysqli_free_result($m_result);
|
|
|
|
|
What does "not working" mean, what are you trying to count?
|
|
|
|
|
You haven't explained what "not working" means. The only notable difference documented in that function between v7 and v8 is:
Prior to PHP 8.0.0, if the parameter was neither an array nor an object that implements the Countable interface, 1 would be returned, unless value was null, in which case 0 would be returned.
So either you're now getting a TypeError where previously you were just counting the number of rows; you're now getting the sum of the string lengths, whereas previously you were counting the number of rows; or something else is happening, which you haven't explained.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You are correct, previously it was counting number of rows, now it's counting string lenghths which is not the requirement.
Please suggest me a solution
|
|
|
|
|
Replace:
$total += count($row[0]); with:
$total += 1;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes it worked!!
Thank you!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
