GoFuckYourself.com - Adult Webmaster Forum

- Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)

- - Tech Dumb SQL question (https://gfy.com/showthread.php?t=1173423)

Dumb SQL question

This should pick up any pattern that shows up in the search string right?

Code:

$result = mysql_query("SELECT url, title FROM fappageinfo WHERE title LIKE '%{$searchstring}%' ORDER BY dateadded DESC") or die('error database');

For some reason, It isn't picking up the first word in the column. I tried

Code:

$result = mysql_query("SELECT url, title FROM fappageinfo WHERE title LIKE '%{$searchstring}%' OR title LIKE '{$searchstring}%' ORDER BY dateadded DESC") or die('error database');

But this didn't work either.

For example title "transvestite threesome tea party"

Search returns for "threesome" or "tea" but not transvestite.

Oh yea, Here is the site.
Fap Rush - Adult Website Listing

I don't have much in the database yet, only 4 or 5 sites. Search for lesbian, snake or tea should bring back results.

You should set up the query first and then execute mysql_query($query);

eg:

Code:

<?php

// This could be supplied by a user, for example

$firstname = 'fred';

$lastname  = 'fox';



// Formulate Query

// This is the best way to perform an SQL query

// For more examples, see mysql_real_escape_string()

$query = sprintf("SELECT firstname, lastname, address, age FROM friends 

    WHERE firstname='%s' AND lastname='%s'",

    mysql_real_escape_string($firstname),

    mysql_real_escape_string($lastname));



// Perform Query

$result = mysql_query($query);



// Check result

// This shows the actual query sent to MySQL, and the error. Useful for debugging.

if (!$result) {

    $message  = 'Invalid query: ' . mysql_error() . "\n";

    $message .= 'Whole query: ' . $query;

    die($message);

}



// Use result

// Attempting to print $result won't allow access to information in the resource

// One of the mysql result functions must be used

// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.

while ($row = mysql_fetch_assoc($result)) {

    echo $row['firstname'];

    echo $row['lastname'];

    echo $row['address'];

    echo $row['age'];

}



// Free the resources associated with the result set

// This is done automatically at the end of the script

mysql_free_result($result);

?>

show us

Code:

while ($row = mysql_fetch_assoc($result)) {

    echo $row['url'];

    echo $row['title'];

}

btw mysql_query() is deprecated.

PHP: mysql_query - Manual

Quote:

Originally Posted by AdultKing (Post 20571769)

btw mysql_query() is deprecated.

PHP: mysql_query - Manual

:thumbsup I did not know this. I am gonna go back and rewrite in mysqli. Thanks AdultKing.

looks way better. SQLI isn't deprecated, right?

Code:

//SQL Connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

} 





$sql = "SELECT url, title FROM fappageinfo WHERE title LIKE '%{$searchstring}%' OR title LIKE '{$searchstring}%' ORDER BY dateadded DESC";

$result = $conn->query($sql);



if ($result->num_rows > 0) {

    // output data of each row

    while($row = $result->fetch_assoc()) {

      echo "<div>";

      echo "<a href=\"http://www.faprush.com/";

      echo $row['url'];

      echo "\"";

      echo " target=\"_blank\"";

      echo ">";

      echo $row['title'];

      echo "</a>";

      echo "</div>"; 

}

}



// Closing connection

$conn->close();

Quote:

Originally Posted by lezinterracial (Post 20571791)

looks way better. SQLI isn't deprecated, right?

Code:

//SQL Connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

} 





$sql = "SELECT url, title FROM fappageinfo WHERE title LIKE '%{$searchstring}%' OR title LIKE '{$searchstring}%' ORDER BY dateadded DESC";

$result = $conn->query($sql);



if ($result->num_rows > 0) {

    // output data of each row

    while($row = $result->fetch_assoc()) {

      echo "<div>";

      echo "<a href=\"http://www.faprush.com/";

      echo $row['url'];

      echo "\"";

      echo " target=\"_blank\"";

      echo ">";

      echo $row['title'];

      echo "</a>";

      echo "</div>"; 

}

}



// Closing connection

$conn->close();

It isn't necessarily your use of mysql_query that is your problem in getting the right results from your SQL query , but it's good practice to set up the query first and make sure you escape all input and then submit the query.

Are you getting the output you want now ? I checked your website and it's still not returning matches for any word in the query.

Quote:

Originally Posted by AdultKing (Post 20571814)

I see what you are saying. Using prepared statements and then executing with escaped input helps against SQL Injection.

No, Still not working. But I want to try using parameterized SQL now. Kind of difficult to find good examples of the correct way to do things and too much old code on the internet.

Quote:

Originally Posted by lezinterracial (Post 20571846)

Show us what you have so far.

I use Laravel now so my raw MySQL query knowhow is rusty, Laravel spoils you like that. Maybe some other coders on GFY could chime in and tell me if I've missed something fundamental, it's likely.

Database: Query Builder - Laravel - The PHP Framework For Web Artisans

transvestite returns a result for me. Also I personally like working with PDO as an alternative, no need to switch just throwing that out there as an option.

Quote:

Originally Posted by PornWorx (Post 20571852)

transvestite returns a result for me. Also I personally like working with PDO as an alternative, no need to switch just throwing that out there as an option.

Not bringing back the page though. If you type in tea, the result brings back the page. PDO looks pretty interesting.

Quote:

Originally Posted by AdultKing (Post 20571850)

Besides the data validation and sanitizing the output, The above code is pretty much all I got now. I'm gonna look more into prepared statements. Looks like that is the professional way to do things now. I get sidetracked like this, but its cool cause I'm learning stuff.

Thanks for your help fellas.

Search for transvestite on your front page returns a result for me. Searching again on search.php does not. Maybe compare the two pages?

Sorry the two pages are the same. The problem is the extra whitespace in your input field. Remove that space and use the php trim () function to remove any extra white space from your search terms after submitting the form.

Me, I would try something like this:

<?php
$searchstring='transvestite threesome tea party';

$terms=explode(' ',$searchstring);

$conditions=array();

for($i=0;$i<count($terms);$i++)
{
if(!empty($terms[$i]))
{
$conditions[$i]="title like '%" . trim($terms[$i]) . "%'";
}
}

$sql_str ="select url, title from fappageinfo where ";
for($i=0;$i<count($conditions);$i++)
{
if($i>0)
{
$sql_str .=" or ";
}
$sql_str .=$conditions[$i] . " ";
}
echo "sql=" . $sql_str . "<br>";

?>

the echo produces: sql=select url, title from fappageinfo where title like '%transvestite%' or title like '%threesome%' or title like '%tea%' or title like '%party%'

then use whatever flavor you want to exec the query, mysql, msqli, PDO, whatever

Hope that helps

Quote:

Originally Posted by sarettah (Post 20571910)

Nice :thumbsup

I was confused by the syntax of '%{...}%' as I had never seen that before. Researched a little, saw some folks using it, could not find anything official so I set up a little test to see what would happen.

I first run the query using the routine I did above.

I then run it using the original syntax.

My test database looks like this:

`test_table` (`id`, `url`, `title`)
(1, 'test url 1', 'transvestite threesome tea party'),
(2, 'test url 2', 'threesome tea party'),
(3, 'test url 3', 'tea party'),
(4, 'test url 4', 'party'),
(5, 'test url 5', 'tea for me'),
(6, 'test url 6', 'part time for transvestites');

Mo code (at http://madspiders.com/gfy_mysql_test/test.php) looks like this:

Code:

<?php



echo "start of program<br><br>\n";



//error_reporting(0);



echo "In new code<br>\n";



require_once('srchdbfunctions.php');



echo "hooking up<br>\n";



$db=hookup('gfy_test');



if($db)

{

  echo "got db <br>\n";

}



echo "past connect<br>\n";



$searchstring='transvestite threesome tea party';



echo "searchstring=" . $searchstring . "<br>\n";



$terms=explode(' ',$searchstring);



echo "Constructing conditions<br>\n";



$conditions=array();

for($i=0;$i<count($terms);$i++)

{

  if(!empty($terms[$i]))

  {

    $conditions[$i]="title like '%" . trim($terms[$i]) . "%'";

  }

}



echo "Constructing sql str<br>\n";



$sql_str  ="select id, url, title from test_table where ";

for($i=0;$i<count($conditions);$i++)

{

  if($i>0)

  {

    $sql_str .=" or ";

  }

  $sql_str .=$conditions[$i] . " ";

}

echo "sql string=" . $sql_str . "<br>";



$stmnt=$db->query($sql_str);



echo "Sql returned " . $stmnt->rowcount() . " rows<br><br>\n";



while($result=$stmnt->fetch(PDO::FETCH_ASSOC))

{

  echo "Rec " . $result['id'] . " Title: " . $result['title'] . " matched <br>\n";

}



echo "<br><br>";





//version 2

?>



Broke this out of html to show how the sql str is being constructed:



$sql_str  ="Select id, url, title from test_table ";

$sql_str .="where title LIKE '%{$searchstring}%'";

<br><br>

As soons as we show it in php the brackets {} disappear:

<br><br>

<?php



echo "sql starts back here:<br>\n";

$sql_str  ="Select id, url, title from test_table ";

$sql_str .="where title LIKE '%{$searchstring}%'";



echo "sql=" . $sql_str . "<br><br>\n";



echo "when we go to execute that we get back:<br><br>";



$stmnt=$db->query($sql_str);



echo "Sql returned " . $stmnt->rowcount() . " rows<br><br>\n";



while($result=$stmnt->fetch(PDO::FETCH_ASSOC))

{

  echo "Rec " . $result['id'] . " Title: " . $result['title'] . " matched " . $result['title'] . "<br>\n";

}



echo "End of program<br>\n";



?>

This is the oputput:

start of program

In new code
hooking up
got db
past connect
searchstring=transvestite threesome tea party
Constructing conditions
Constructing sql str
sql string=select id, url, title from test_table where title like '%transvestite%' or title like '%threesome%' or title like '%tea%' or title like '%party%'
Sql returned 6 rows

Rec 1 Title: transvestite threesome tea party matched
Rec 2 Title: threesome tea party matched
Rec 3 Title: tea party matched
Rec 4 Title: party matched
Rec 5 Title: tea for me matched
Rec 6 Title: part time for transvestites matched

Broke this out of html to show how the sql str is being constructed: $sql_str ="Select id, url, title from test_table "; $sql_str .="where title LIKE '%{$searchstring}%'";

As soons as we show it in php the brackets {} disappear:

sql starts back here:
sql=Select id, url, title from test_table where title LIKE '%transvestite threesome tea party%'

when we go to execute that we get back a:

Sql returned 1 rows

Rec 1 Title: transvestite threesome tea party matched

End of program

Thread like this restores my faith in GFY.

Not the way I do my queries, but very smart. Will make all new queries in a similar way now...

Quote:

Originally Posted by sarettah (Post 20571910)

I got pretty wordy in there because I usually try to make the code in here as readable as possible. A more concise implementation of that would be:

<?php
$searchstring='transvestite threesome tea party';
$terms=explode(' ',$searchstring);
$whereclause='';
foreach($terms as $term){$whereclause .=(!empty($term)?(!empty($whereclause)?"or ":'') . "title like '%" . trim($term) . "%' ":'');}
$sql_str="select url, title from fappageinfo " . (!empty($whereclause)?'where ' . $whereclause:'');;
echo "sql=" . $sql_str . "<br>\n";
?>

It is exactly the same, but different.

.

Quote:

Originally Posted by PornWorx (Post 20571888)

Yea, You may have tried my search engine before when I hard coded transvestite into the SQL to see what would happen.

Anywho, Thanks PornWorx. And sarettah for taking the time to make that sweet SQL for me. I will get around to trying it sometime. Friends and family coming and hanging out this week, So I'm not getting anytime to play with my code.

tl;dr: the code snippets.

It's been a little while since I've had to do any heavy lifting with MySQL, but if I recall correctly, when you use % in your database query, it assumes that you're looking for something before the beginning of the string. Therefore anything that starts with your string, and nothing before it, it will return 0 results.

Quote:

Originally Posted by drclockwork (Post 20572931)

That is incorrect.

https://dev.mysql.com/doc/refman/5.0...functions.html
% matches any number of characters, even zero characters.

from our examples above:

sql=Select id, url, title from test_table where title LIKE '%transvestite threesome tea party%'

when we go to execute that we get back a:

Sql returned 1 rows

Rec 1 Title: transvestite threesome tea party matched

Quote:

Originally Posted by PornWorx (Post 20571888)

:thumbsup
This worked.

Code:

$trimstring = trim($outputsearchstring, " \t\n\r\0\x0B");



$sql = "SELECT url, title FROM fappageinfo WHERE title LIKE '%$trimstring%' ORDER BY dateadded DESC";

Thanks again. PornWorx, AdultKind, sarettah, suesheboy, and drclockwork. For taking the time to read and offer advise in my post. I have learned a lot.