Learn VBA & Macros in 1 Week!

PHP - Filtering Inner Join Data

Full Excel VBA Course - Beginner to Expert

Filtering Inner Join Data	View Content

I'm using an INNER JOIN to grab some data from 2 tables depending on if another's id matches an id within the other table (obviously.. ).

Is there anyway to continue on with this join but to filter even deeper by only taking data that not only has matching some_id but also does not have a row that equals a certain value?

Something along the lines of adding "WHERE some_row != 'value'" ?

"SELECT * FROM my_firstTable INNER JOIN my_secondTable ON my_firstTable.some_id = my_secondTable.some_id ORDER BY my_firstTable.some_id"

Full Excel VBA Course - Beginner to Expert

Filtering Data

Similar Tutorials

View Content

hey guys i was generally wondering...is it good practice to add_slashes and use mysqli_real_escape_string when entering data into the database? then to strip slashes when extracting rows? is this the right way to go around things...thanks

Php Data Filtering From Db

Similar Tutorials

View Content

Hi, I have an issue, I cant filter my data from DB. Whats wrong in my code?
<?php
/* Include Files *********************/
//include("banners/database.php");
mysql_connect("host", "user", "pwd") or die("Connection Failed");
mysql_select_db("DB")or die("Connection Failed");
?>
<?php
$memberIndustries =['memberIndustries'];
if (isset($_POST["memberIndustries"])) {
$memberIndustries = mysql_real_escape_string ($_POST["memberIndustries"]);
$sql="SELECT * FROM Members WHERE Industries_Industry_ID='$memberIndustries'";
$result = mysql_query($sql) or die (mysql_error());

while ($myrowe = mysql_fetch_array($result)) {?>
 <? // if new member, label it
 //$todays_date = date("Y-m-d");
 //$dateCompare = $myrowe[Creation_Date]+30;
 //$dateCompare = strtotime(date("Y-m-d", strtotime($myrowe[Creation_Date])) . " +30 days");
 //if(strtotime($todays_date)<=$dateCompare){
 ?>
 <table width="100%">
 <tr>
 <td align="right">
 <img src="/members/new.gif">
 </td>
 </tr>
 </table>
 <? //}?>

 <? if($myrowe[LOGO_lnk]!='') {?>
 <img src="<? echo $myrowe[LOGO_lnk]; ?>">
 <? } ?>
 
 
 <? echo $myrowe[Company_Title];?>
 
 <? if($myrowe[Person_Name_1]!='') {?>
 
 <? echo $myrowe[Person_Name_1];?>
 <? echo $myrowe[Person_Title_1];?>
 <? }
 if($myrowe[Person_Name_2]!='') {?>
 
 <? echo $myrowe[Person_Name_2];?>
 <? echo $myrowe[Person_Title_2];?>
 <? }
 if($myrowe[Person_Name_2]!='') {?>
 
 <? echo $myrowe[Person_Name_3];?>
 <? echo $myrowe[Person_Title_3];?>
 <? }
 if($myrowe[Address_row1]!='') {?>
 
 <? echo $myrowe[Address_row1];?>
 <? }
 if($myrowe[Address_row_2]!='') {?>
 
 <? echo $myrowe[Address_row_2];?>
 <? }
 if($myrowe[Address_row_3]!='') {?>
 
 <? echo $myrowe[Address_row_3];?>
 <? }
 if($myrowe[Phone]!='') {?>
 
 Tel:<? echo $myrowe[Phone];?>
 <? }
 if($myrowe[Fax]!='') {?>
 
 Fax:<? echo $myrowe[Fax];?>
 <? }
 if($myrowe[email]!='') {?>
 
 <a href="mailto:<? echo $myrowe[email];?>">
 <? echo $myrowe[email];?>
 </a>
 <? }
 if($myrowe[web]!='') {?>
 
 <a href="<? echo $myrowe[web];?>"target="_blank">
 <? echo $myrowe[web];?>
 </a>
 <? }
 if($myrowe[Industries_Industry_ID]!=0) {
 $queryInd = "SELECT Industry_Title FROM Industries WHERE Industry_ID=$myrowe[Industries_Industry_ID]";
 $resultInd = mysql_query($queryInd) or die (mysql_error());
 $myroweIND = mysql_fetch_array($resultInd)
 ?>
 
 <? echo $myroweIND[Industry_Title];?>
 <? }
 if($myrowe[Description]!='') {?>
 
 <? echo $myrowe[Description];?>
 <? } ?>
 
<? }
<? }} ?>

Left Join Data Not Displayed?

Similar Tutorials

View Content

I am trying to write some code to display the visits to my site each week in batches of 13 weeks (1 quarter). I am getting the Week No and the number of hits, but the actual date, which is in a separate table is not being displayed. I have tried LEFT JOIN and INNER Join and even RIGHT JOIN (this only displays the Date). I can't see where I am going wrong! Here is the code:

<?
include('connect_visits.php');
doDB7();
$limitStart = $_POST['QUARTER'] - 13;
$Visit_data="SELECT Visits.ID, Visits.WVisits, Visits.WCom, WeekNo.WNo, WeekNo.WCom
        FROM Visits 
        LEFT JOIN WeekNo
            ON Visits.ID = WeekNo.WCom
        ORDER BY Visits.ID
	 LIMIT {$limitStart}, 13";
$Visit_data_res = mysqli_query($mysqli, $Visit_data) or die(mysqli_error($mysqli));
$display_block = "
<table width=\"20%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" >
	<tr>
	<th>Week No</th>
	<th>Week Commencing</th>
	<th>Visits</th>
		</tr>";	
while ($C_info = mysqli_fetch_array($Visit_data_res)) {
		$Cid = $C_info['ID'];
		$Visits = ($C_info['WVisits']);
		$WeekNo = ($C_info['WCom']);
//add to display
$display_block .= "
<tr>
<td width=\"1%\" valign=\"top\">".$Cid."<br/></td>
<td width=\"8%\" valign=\"top\">".$WeekNo."<br/></td>
<td width=\"5%\" valign=\"top\">".$Visits."<br/></td>
</td></tr>";
}
mysqli_free_result($Visit_data_res);
?>

Where am I going wrong?
Edited by rocky48, 29 December 2014 - 12:53 PM.

Adding Data And Join Tables

Similar Tutorials

View Content

I have two tables 'book' and 'category'. They look like the following in phpmyadmin; book id title author category isbn
---- ------- ---------- ---------- -------
1 Treasure Chest Jim Jones 1 14252637 2 Pirates Boat Sue Smith 2 88447737 3 Adventure Land Harry Jo 3 01918273 4 Winter Week Sam Dill 3 00999337 5 The Twite Roald Dahl Fiction 87873366 category id cat_name
---- -------
1 Horror 2 Kids 3 Fiction 4 Science Users have the option of adding books into the library via an online form, or via a Google Booka api method (user enters isbn, searches, is presented with book info and then clicks 'add to library', done.). This is handled via ajax. The online form works fine, and successfully adds the book info. However my problem is with the Google Books method, it successfully adds the data to the db however the category column is in text format (i.e 'Juvenile Science' or 'Scary Fiction') as opposed to the manual form which adds categories as 1, 2 or 3 (1 =Horror, 2 = Kids, 3 = Fiction). Is there any way I can add the Google Book category data to my table and convert it to an integer or similar? Not sure what I need to do. Suggestions appreciated! Should I add the Google entries to another table (i.e googleCategory)? My HTML only outputs the numbered category entries and ignored the text format entries. my php

$sql = "SELECT b.id, b.title, b.author, b.isbn, b.publicationYear,
        c.cat_name
        FROM book AS b
        INNER JOIN category AS c ON b.category = c.id
        WHERE status != 'Archive' ORDER BY id DESC LIMIT $startrow, 15 ";


$res = $conn->query($sql) or trigger_error($conn->error."[$sql]");


while($row = $res->fetch_array()) {


    echo '<tbody>';
    echo '<tr>';
    echo '<td>' . $row['id'] . '</td>';
    echo '<td>' . $row['title'] . '</td>';
    echo '<td>' . $row['author'] . '</td>';
    echo '<td>' . $row['cat_name'] . '</td>';
    echo '<td>' . $row['isbn'] . '</td>';
    echo '<td>' . $row['publicationYear'] . '</td>';
    echo '</tr>';
    echo '</tbody>';
};

Apologies if this is all a bit confusing I am very new to php and mysql. Thanks, J

Join And Use Unique Data From Same Column Names

Similar Tutorials

View Content

Join Mysql Tables, Echo Data Based On Different Tables

Similar Tutorials

View Content

This portion is kind of stumping me. Basically, I have a two tables in this DB: users and users_access_level (Separated for DB normalization)

users: id / username / password / realname / access_level
users_access_level: access_level / access_name

What I'm trying to do, is echo the data onto an HTML table that displays users.username in one table data and then uses the users.access_level to find users_access_level.access_name and echo into the following table data, I would prefer not to use multiple queries if possible or nested queries.

Example row for users: 1234 / tmac / password / tmac / 99
Example row for users_access_level: 99 / Admin

Using the examples above, I would want the output to appear as such:

Username: Access Name: Tmac Admin
I am not 100% sure where to start with this, but I pick up quickly, I just need a nudge in the right direction.

The code I attempted to create just shows my lack of knowledge of joining tables, but I'll post it if you want to see that I did at least make an effort to code this myself.

Thanks for reading!

Moved: Inner Join Vs. Left Join

Similar Tutorials

View Content

This topic has been moved to MySQL Help.

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

Need Help With Filtering

Similar Tutorials

View Content

Filtering

Similar Tutorials

View Content

I have a form and script that is used to collect information from users.
There is one field that is url.
There is one field that is email.

I want to filter out scripts. I also only want to allow url in the url field and email in the email field.

Is there an easy way to filter? I have tried several pieces of code to no avail.

Code: [Select]
<?php
$root = $_SERVER['DOCUMENT_ROOT'];
include('/home/arts/public_html/shows/includes/config.db.php');
if ($link)
{
 foreach ($_POST as $k => $v)
 {
 if (($k == "start_date") || ($k == "end_date") || ($k == "application_dead"))
 {
 $varray = explode("/",$v);
 $v = $varray[2] . "-" . $varray[0] . "-" . $varray[1];
 }

 $v = "'" . addslashes($v) . "'";


 if($k != "captcha_code")
{

$keys[] = $k;
$vals[] = $v;
}
 }

 $fields = implode(",", $keys);
 $values = implode(",", $vals);
 //$fields = "id," . $fields;
 //$values = "null," . $values;

 $sql = "INSERT into craft_shows ($fields) VALUES ($values)";

 $result = mysql_query($sql,$link);
if (!$result) {
 die('There was a problem with your submission. Refresh the page to try again');
}
else
{
 echo "Thank you for your submission. ";
 echo "<a href=\"http://www.artsandcraftsnetwork.com/shows/\">Back to shows</a> ";
 echo "<a href=\"http://www.artsandcraftsnetwork.com/shows/shows_submit.php\">Submit another show</a>";
}


}
?>

Allow '@' And '.' For Email Filtering

Similar Tutorials

View Content

Hello, Is it possible to allow '@' and '.' while maintaining the rest of this regex?

$email= 'adsfsa@asdf.edu';
$email= preg_replace("/[^A-Za-z0-9]/", " ", $email);
echo $email;

Filtering Gps Points

Similar Tutorials

View Content

hi,

i am trying to filter my gps history points.

i would like to ignore points if they are too close to the previous position.

i can do this like so.

$old_lat = $old_long = "0";

foreach($history['data'] as $record) {

//calculate distance in meters
$distance = distance($record['latitude'], $record['longitude'], $old_lat, $old_long, "V");

if($distance >= 300) {
echo 'add(jQuery(this), number += 1, "' . date("d-m-Y @ h:i:s",$record['timestamp']) . '", "map_post.php?n=' . $name . 'u=' . $history['user'] . '", "' . $history['user'] . '", "' . $record['latitude'] . '", "' . $record['longitude'] . '");';

$old_lat = $record['latitude'];
$old_long = $record['longitude'];
}

the problem with the above is that if i arrive home at 6pm on friday and stay outside my house for 24hours, it will show the latest position as at 6pm on fri.

i would like it to show the 6pm friday position but with a date 'from and to' time like (28/10/11 18:00 - 29/10/11 18:00) rarther than just (28/10/11 18:00) which looks inacurate.

Possible?

Filtering Arrays Help

Similar Tutorials

View Content

Hi guys

I was wondering if anyone could point me in the right direction with this.

I am using JSON to return the contents of a directory and then using a foreach loop to print each one to a new row in a table.

Is there anyway to filter the results? Basically I just want to show the files that end in a .jar extension? Currently all sub directories and files are shown, however all the files I need to pull will be in the main directory.

This is what I am using:

Code: [Select]
<?php
foreach ($installedplugins1['success'] as $v)
{
echo "<tr><td>".$v."</td>";
echo "<td><a href='index.php?dp=".$v."'>Disable Plugin</a></td>";

}
?>

Any hints or reading material?

Cheers

Filtering Files

Similar Tutorials

View Content

Hi all,

I was wondering if i can filter certain log files like using 2 keywords and get all the info inbetween? The log files are like <1 MB.

The other problem i encounter is howto since i looked all over the net but cant seem to find usefull info about it.

Can you help me out here?

Moved: Filtering

Similar Tutorials

View Content

This topic has been moved to PHP Regex.

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

Filtering Array

Similar Tutorials

View Content

Hi there

I have the following array .

Code: [Select]
$products = array( array( Code => "CF1",
Items => "Sand Tray",
Cat => 'Tray',
),
array( Code => "CF2",
Items => "Mobile Computer Table",
Cat => 'Table',
),
array( Code => "CF3",
Items => "General Service Trolley",
Cat => 'Trolley',
),
array( Code => "CF4",
Items => "TV Trolley",
Cat => 'Trolley',
),
array( Code => "CF5",
Items => "Overhead Projector Trolley",
Cat => 'Trolley',
),
array( Code => "CF6",
Items => "Book Trolley",
Cat => 'Tolley',
),
array( Code => "CF7",
Items => "Stacking Chairs",
Cat => 'Chairs',
),
);
What I want to do is select each by 'Cat'. For example I simply want to display all the trolleys.

I was wondering how I do this? What functions would I use in PHP?

Thanks a million

How Good Or Bad Is The Sql Filtering?

Similar Tutorials

View Content

I have a SQL statement which is difficult to use PDO on, it might not even be possible to do. So I'm filtering it like this:

$search = $_GET['search'];
$search = preg_replace("/[^A-Za-z0-9]/", " ", $search);
$search = $mysqli->real_escape_string($search);

Will this result in an acceptable level of security?
Edited by anderson_catchme, 16 September 2014 - 12:28 PM.

Utf-8 Filtering/validation

Similar Tutorials

View Content

First off, i have made a validation class using array_diff() to check if there are invalid characters submitted by a user.
Some extracts:
Code: [Select]
$this->chr_alpha_lower=array('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z');
$this->chr_alpha_upper=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
$this->chr_numeric=array('0','1','2','3','4','5','6','7','8','9');
$this->chr_symbol=array(' ','!','@','#','$','%','&','(',')','[',']','.',',',':',';','\'','"','/','=','\\','-','_','?');

...

// lets say only alpha and numeric characters are allowed
$allowed_characters=array();
$allowed_characters=array_merge($allowed_characters,$this->chr_alpha_lower,$this->chr_alpha_upper);
$allowed_characters=array_merge($allowed_characters,$this->chr_numeric);

$input_split=str_split($this->input);
$invalid=array_diff($input_split,$allowed_characters);

if(empty($invalid)){
return true;
}
return false;

I have two questions.
First, for acsii characters is this method 'full proof' or is there a way for someone to get around this validation script?

Second, a new site im developing is going to be built with UTF-8 in mind so people can use the site in their own language. How do i validate UTF-8 user input? I know about mb_strings and sanitizing UTF-8 input using this:
Code: [Select]
preg_match_all('/([\x09\x0a\x0d\x20-\x7e]'. // ASCII characters
'|[\xc2-\xdf][\x80-\xbf]'. // 2-byte (except overly longs)
'|\xe0[\xa0-\xbf][\x80-\xbf]'. // 3 byte (except overly longs)
'|[\xe1-\xec\xee\xef][\x80-\xbf]{2}'. // 3 byte (except overly longs)
'|\xed[\x80-\x9f][\x80-\xbf])+/', // 3 byte (except UTF-16 surrogates)
$input, $clean_pieces );

$clean_output = join('?', $clean_pieces[0] );

But how do i know what to filter?

Example:
a field for the user to input first name
English: only alpha characters
Other language: ?????

Help much appreciated.

Php Filtering Results

Similar Tutorials

View Content

I want to create a feature as you see nn this image, where it says 'filter your results' if a user clicks 'Detached Houses' then only detached houses will be displayed. if a user clicks 'Semi-detached' then only semi detached houses will be shown.
Any help is really appreciated, thank you.
Code: [Select]

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td><div id="filter">Houses
 
Detached Houses<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get a specific result from the "example" table
$result = mysql_query("SELECT * FROM example
WHERE name='Sandy Smith'") or die(mysql_error());

// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result );
// Print out the contents of each row into a table
echo $row['name']." - ".$row['age'];
?>
 
Semi-detached houses
 
Terraced houses
 
 
Flats / Apartments

</div></td>

Parsing / Filtering Xml

Similar Tutorials

View Content

Hi all,

I am working on a tool to pull data remotely and the data is set to xml format. I want to post items that meet a certain criteria only, essentially there are several layers/tags on the data being brought over. The sStatus is the primary item I am looking for, not only that but only if it has or equals to "Waiting". Any thoughts or suggestions greatly appreciated. Below is the basic code I have in place currently and am working on.

<code>
<?php
$soapclient = new SoapClient("https://secure.logmeinrescue.com/api/api.asmx?wsdl");

$sEmail = "dummy@dum.com";
$sPassword = "dum123";
$iNodeID = "123456";
$eNodeRef = "NODE";
$sAuthCode = "";

$loginparams = array (
'sEmail' => $sEmail,
'sPassword' => $sPassword);

$loginResult = $soapclient->login($loginparams);

$output = array(
'eOutput' => "XML"
);

$outputResponse = $soapclient->setOutput($output);

$sessionparams = array(
'iNodeID' => $iNodeID,
'eNodeRef' => $eNodeRef,
'sAuthCode' => $sAuthCode
);

//get session(s)
$sessionresult = $soapclient->getSession($sessionparams);
$session = $sessionresult->aSessions;
$sessionnodes = $session->SESSION;
$session_status = $sessionnodes[$isessionNodes]->sStatus;

print_r($sessionresult);

?>
</code>

Not Filtering Words?

Similar Tutorials

View Content

I'm pretty sure the problem is starring me in the face, but I can't seem to locate it.

function filter($string)
{
//swear words pulled from bannedwordlist.com
$f = fopen('../badwords.txt', 'r');
$bad_words = fread($f, filesize('../badwords.txt'));
$bad_words = explode('\n', $bad_words);

$input = strtolower($string);

foreach($bad_words as $value)
{
$string = str_replace($value, '****', $input);
}

return $string;
}

UPDATED CODE

function filter($string)
{
//swear words pulled from bannedwordlist.com
$f = fopen('../badwords.txt', 'r');
$bad_words = fread($f, filesize('../badwords.txt'));
$bad_words = explode('\n', $bad_words);

$input = strtolower($string);
$string = str_replace($bad_words, '****', $input);

return $string;
}

I've already echoed out $value in the foreeach loop, and it does correctly retrieve the bad words and put them into an array. My only problem is, the returned string is still in strtolower() form, and the words aren't censored. :/