PHP - Moved: Mysql Query Not Filtering

Last Christmas my wife bought me a book on html and css because I said that I have always wanted to write a website. Over the last 10 months I have really enjoyed learning web design and have progressed to try and learn php and using myPHPadmin databases.   I am now trying to write a private message function.

I've got a members database and a conversation database. I am trying to filter out whether a private message is 'read' or 'unread', but whatever I try it is still listing all messages under both options. I've spent 48 hours staring at this bit of code. Can anyone see what I have done wrong?

The relevant (and self-explanatory) fields from my members database a
id
username
member_first_name
member_last_name

The relevant fields from my conversation database a
id
id2 (always set as '1' to count messages in a conversation)
member1 (the initial sender of the first message)
member2 (the initial recipient of the first message)
read1 (member1 has read this message default to yes when message sent and to no when message received)
read2 (member2 has read this message default to yes when message sent and to no when message received)
removed1 (member1 has deleted this message from their record)
removed2 (member2 has deleted this message from their record)
time (timestamp)
 

<?php
$query1 = DB::getInstance()->query("SELECT c1.id, c1.title, c1.time, c1.removed1, c1.removed2, count(c2.id) AS reps, 
                    m.id as memberid, m.username m.member_first_name, m.member_last_name
                    FROM conversation as c1, conversation as c2, members as m
                    WHERE ((c1.member1='{$logid}' AND c1.read1='No' AND c1.removed1='No'
                    AND m.id=c1.member2)
                    OR (c1.member2='{$logid}' AND c1.read2='No' AND c1.removed2='No'
                    AND m.id=c1.member1))
                    AND c1.id2='1' AND c2.id=c1.id
                    GROUP BY c1.id
                    ORDER BY c1.id DESC");
$query2 = DB::getInstance()->query("SELECT c1.id, c1.title, c1.time, c1.removed1, c1.removed2, count(c2.id) as reps, 
                    m.id as memberid, m.username, m.member_first_name, m.member_last_name
                    FROM conversation as c1, conversation as c2, members as m
                    WHERE ((c1.member1='{$logid}' AND c1.read1='Yes' AND c1.removed1='No'
                    AND m.id=c1.member2)
                    OR (c1.member2='{$logid}' AND c1.read2='Yes' AND c1.removed2='No' AND
                    m.id=c1.member1))
                    AND c1.id2='1' AND c2.id=c1.id
                    GROUP BY c1.id
                    ORDER BY c1.id DESC");
?>

UNREAD MESSAGES (<?php echo intval($query1->count()); ?>)
<br>
READ MESSAGES (<?php echo intval($query2->count()); ?>)

I've tried running through things in a logical path:   UNREAD MESSAGES member1 = logged in user read1 = No removed1 = No members.id = member2       OR member2 = logged in user read2 = No removed2 = No members.id = member1   READ MESSAGES member1 = logged in user read1 = Yes removed1 = No members.id = member2       OR member2 = logged in user read2 = Yes removed2 = No members.id = member1   I can't understand how if 'read1 = yes and the logged in user is member1, why they are being counted in the unread list.   Can anyone see what I have done wrong?
Edited by MartynLearnsPHP, 15 November 2014 - 08:05 AM.

Hello,

I need help filtering an SQL query based on the combination of drop down menus. I have tried using this code found in another thread but I am still getting all rows selected. Any ideas?? Thank you.

Here is my html
Code: [Select]
<form name="xml.php" method="POST">
            <input type="button" id="showmarkers" value="Show Markers" />&nbsp;&nbsp;
                <select name="meetingType">
                <option value="All Types" selected="All Types">All Types</option>           
                <option value="fun">fun</option>
                <option value="work">work</option>
                </select>&nbsp;&nbsp;
                <select name="meetingDay">
                <option value="All Days" selected="All Days">All Days</option>
                <option value="Monday">Monday</option>
                <option value="Tuesday">Tuesday</option>
                <option value="Wednesday">Wednesday</option>
                <option value="Thursday">Thursday</option>
                <option value="Friday">Friday</option>
                <option value="Saturday">Saturday</option>
                <option value="Sunday">Sunday</option>
                </select>&nbsp;&nbsp;
                <select name="meetingTime">
                <option value="All Times" selected="All Times">All Times</option>
                <option value="Early">Early</option>
                <option value="Noon">Noon</option>
                <option value="Late">Late</option>
                </select>
</form>

And the PHP:
Code: [Select]
$whereClauses = array();
if (! empty($_POST['meetingType'])) $whereClauses[] ="meetingType='".mysql_real_escape_string($_POST['meetingType'])."'";
if (! empty($_POST['meetingDay'])) $whereClauses[] ="meetingDay='".mysql_real_escape_string($_POST['meetingDay'])."'";
if (! empty($_POST['meetingTime'])) $whereClauses[] ="meetingTime='".mysql_real_escape_string($_POST['meetingTime'])."'";
$where = '';
if (count($whereClauses) > 0) { $where = ' WHERE '.implode(' AND ',$whereClauses); }
$resultID = mysql_query("SELECT * FROM meetings".$where);

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=308528.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=328883.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=313679.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=322237.0

This topic has been moved to Ajax Help.

http://www.phpfreaks.com/forums/index.php?topic=347585.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=312690.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=342695.0

This topic has been moved to PHP Regex.

http://www.phpfreaks.com/forums/index.php?topic=334433.0

This topic has been moved to PHP Regex.

http://www.phpfreaks.com/forums/index.php?topic=317962.0

Hi Everybody,

I think it's fairly simple, but i am a noob in PHP so need help from advanced coders.

I have MySQL table which is being filled up everyday, there are 7 fields in the table (submitter, choosebank, choosetype, ampaid, amret, date, descript).
What I need is to create a search.php which will be looking up data in all fields and printing it on the screen and the search should be filtered. What I mean is when a user wants to search for data let's say using submitter  who's name is Mitchel then it should search for all data and print all rows containing submitter (Mitchel) and the rest six fields. And if user searches let's say using submitter (Mitchel) and a certain date (let's say DEC-25-2011) then it should find all data containing Mitchel and DEC-25-2011 in the same row and print all rows which contain the same search parameter and the rest of the fields.

Any help would be greatly appreciated!

Here is the code I have so far, but it doesn't work.

<code>

<?php

include ("blocks/db.php");

$sql = mysql_query("SELECT * FROM banks WHERE submitter LIKE '$sub' or choosebank LIKE '$bank' or choosetype LIKE '$type' or $money_column LIKE '$money_column' or $money_value LIKE '$money_amount' or inputdate LIKE '$inputdate' or descript LIKE '$descript'") or die(mysql_error());

echo "<table width='1000' border='1' cellspacing='0' cellpadding='1'>";
 echo "<tr>";
 echo "<td width='100'>Submitter</td>";
 echo "<td width='90'>Bank</td>";
 echo "<td width='110'>Type of Payment</td>";
 echo "<td width='110'>Amount Paid</td>";
 echo "<td width='110'>Amount Returned</td>";
 echo "<td width='100'>Date</td>";
 echo "<td width='366'>Description</td>";
 echo "</tr>";
echo "</table>";

while ($row = mysql_fetch_array($sql)){

echo "<table width='1000' border='1' cellspacing='0' cellpadding='1'>";
 echo "<tr>";
 echo "<td width='100'>$row[submitter]</td>";
 echo "<td width='90'>$row[choosebank]</td>";
 echo "<td width='110'>$row[choosetype]</td>";
 echo "<td width='110'>$row[ampaid]</td>";
 echo "<td width='110'>$row[amret]</td>";
 echo "<td width='100'>$row[date]</td>";
 echo "<td width='366'>$row[descript]</td>";
 echo "</tr>";
echo "</table>";

}

?>

</code>

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>

I'm restarting this under a new subject b/c I learned some things after I initially posted and the subject heading is no longer accurate.

What would cause this behavior - when I populate session vars from a MYSQL query, they stick, if I populate them from an MSSQL query, they drop.

It doesn't matter if I get to the next page using a header redirect or a form submit.  I have two session vars I'm loading from a MYSQL query and they remain, the two loaded from MSSQL disappear. 

I have confirmed that all four session vars are loading ok initially and I can echo them out to the page, but when the application moves to next page via redirect or form submit, the two vars loaded from MSSQL are empty. 

Any ideas?