Help (if search exists update else insert)

Having real trouble writing this script.

I am trying to use the title e.g. site.com/search.php?q=foo

Then search to see if 'foo' exists.

If it does exist, I need to add 1 to 'views' (This is to create a system to display the most searched terms kinda like the Yahoo Trends system)

I have the following:

	  // Get the search variable from URL
	$s = @$_GET['s'];
	  $var = @$_GET['q'] ;
	  $trimmed = trim($var); //trim whitespace from the stored variable

PHP:

This works fine, but everything I write thereafter doesn't work...

$sql = 'SELECT COUNT(*) AS total FROM searches WHERE title = \'$trimmed\';';
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
$total = $row['total'];
$id = $row["id"];
$title = $row["title"];
if($total==1) {
echo"".$row['title']."";
} else {
echo"Not Exists";
} 

PHP:

Please please please help. I am at crossroads with this new website and cannot move on until I get this script working.

Thanks guys!

Love yall.

 

After you check if it exists and it does, this will add 1 count to views/total.

$count = $row["total"];
$count = $count+1;
$query = "UPDATE searches SET total = '$count' WHERE id = '$id'";

PHP:

Not sure what WHERE condition to use, but you will see from above how to update the Views count.

Anyway something like this should work:

if($total==0) {
echo"Not Exists";
} else {
$count = $row["total"];
$count = $count+1;
$query = "UPDATE searches SET total = '$count' WHERE id = '$id'";
}

PHP:

Hope this helps....

 

MyVodaFone

Thanks for the help, doesn't work though, it's just repeated "not exist"

	  // Get the search variable from URL
	$s = @$_GET['s'];
	  $var = @$_GET['q'] ;
	  $trimmed = trim($var); //trim whitespace from the stored variable
	$sql = 'SELECT COUNT(*) AS total FROM searches WHERE title = \'$trimmed\';';
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
//if doesn't exist
if($total==0) {
echo"Not Exists";
}
//if does exist
 else {
$count = $row["views"];
$count = $views+1;
$query = "UPDATE searches SET views = '$count' WHERE title = '$trimmed'";
}

PHP:

 

What is the default views set at in your database, it should be 1 when its created ?

$count = $views+1; should be $count = $count+1; ie your adding 1 to the original count, also is the views column called views or total ?

...and you left out part of your code declaring

$total = $row['total'];
$id = $row["id"];

 

Thanks for the help, I do appreciate it.

I reverted back to what you said:

	  // Get the search variable from URL
	$s = @$_GET['s'];
	  $var = @$_GET['q'] ;
	  $trimmed = trim($var); //trim whitespace from the stored variable
	$sql = 'SELECT * FROM searches WHERE title = \'$trimmed\';';
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
$total = $row['total'];
$id = $row["id"];
$title = $row["title"];
//if doesn't exist
if($total==0) {
echo"Not Exists";
}
//if does exist
 else {
$count = $row["views"];
$count = $count+1;
$query = "UPDATE searches SET views = '$count' WHERE title = '$title'";
}
echo"$title";
echo"$id";

PHP:

At the bottom, I've tried outputting from the db so I'm guessing it isn't pulling it from the db. Sorry for the noobishness, I try to avoid databases where I can.

You have any other ideas?

 

Its important that we clarify what the columns/rows are called, I will assume that the total views count is in a column called 'total', if not, amend the code according-ling

// Get the search variable from URL
    $s = @$_GET['s'];
      $var = @$_GET['q'] ;
      $trimmed = trim($var); //trim whitespace from the stored variable
    $sql = 'SELECT * FROM searches WHERE title = \'$trimmed\';';
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
$total = $row['total'];
$id = $row["id"];
$title = $row["title"];
//if doesn't exist
if($total==0) {
echo"Not Exists";
}
//if does exist
 else {
$count = $row["total"];
$count = $count+1;
$query = "UPDATE searches SET total = '$count' WHERE title = '$title'";
}
echo"$title";
echo"$id";
echo"$total"; // does this echo anything

PHP:

 

I have the following rows:

ID - int - auto increment
Title - varchar
Views - int - default=1

I tried the new query but to no avail. I changed total to views as that is the row we are updating if it does exist.

Here is all of the code on this page:

<?php
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("******","******","*******"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("******") or die("Unable to select database"); //select which database we're using;
?>


<?php 
include"includes/head.php";
include"includes/top.php";
include"includes/right.php";
?>
<?php

// Get the search variable from URL
    $s = @$_GET['s'];
      $var = @$_GET['q'] ;
      $trimmed = trim($var); //trim whitespace from the stored variable
    $sql = 'SELECT * FROM searches WHERE title = \'$trimmed\';';
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
$views = $row['views'];
$id = $row["id"];
$title = $row["title"];
//if doesn't exist
if($views==0) {
echo"Not Exists";
}
//if does exist
 else {
$count = $row["views"];
$count = $count+1;
$query = "UPDATE searches SET views = '$count' WHERE title = '$title'";
}
echo"$title";
echo"$id";
echo"$views"; // does this echo anything
echo"<br><br>$var"; //checking if it is grabbing from url

	?>

	
	<?php include"includes/footer.php";?>

PHP:

http://www.crazyassthings.com/search.php?q=test

I tested if it was pulling from the url $var and it is...

$var is the search term stored as 'q' in the url.

 

Change

$views = $row['views'];

PHP:

to

$views = $row["views"];

PHP:

Apart from that its still not echoing the ID or views but try the edit

BTW test doesn't exist anyway so your tests should be on HeyWAY ?

 

Still didn't work.

I've created something similar but a lot more simple:

	$views = "UPDATE `searches` SET `views` = `views`+1 WHERE `title` = '".mysql_real_escape_string($_GET['q'])."' ";
	mysql_query($views);

PHP:

Now in order for it to work the way I need to, I need to enter the searched item into the database. I really do need to prevent duplicates though.. This is what I want to use to insert it into the db:

mysql_connect("******", "******", "*******") or die(mysql_error()); 
mysql_select_db("******") or die(mysql_error()); 
mysql_query("INSERT INTO `searched` VALUES (
'$title',
'$id',
'$views'
)
"); 

PHP:

Do you have any idea how to do this but prevent duplicates?

Thank you so much

 

<?php

// Create the MySQL Connection.
if(!$link = mysql_connect('localhost', 'username', 'password')
	die('Could not connect to SQL Server');

// Select our database.
if(!mysql_select_db('database'))
	die('Could not connect to database.');

// Include our dependencies.
include 'includes/head.php';
include 'includes/top.php';
include 'includes/right.php';

// Retrieve get variables.
// WARNING: NOT CLEAN, CAN BE EXPLOITED.
// filter_input(INPUT_GET, 's');
if(isset($_GET['s']) && !empty($_GET['s']))
	$search = $_GET['s'];

if(isset($_GET['q']) && !empty($_GET['q']))
	$q = trim($_GET['q']);

// Select the proper page from the database.
$sql = sprintf("SELECT `id`, `title`, `views` FROM `searches` WHERE `title` = '%s' LIMIT 1;", $q);
$query = mysql_query($sql, $link);
$row = mysql_fetch_assoc($query);

// Extract the returned query with short variables.
extract($row);

// If the views is at least 1 or more..
if($views > 0)
{
	// Update it to implement a page view.
	$sql = sprintf("UPDATE `searches` SET `views` = `views`+1 WHERE `title` = '%s' LIMIT 1;", $title);
	mysql_query($sql, $link);

	echo $title;
	echo $id;
	echo $views;
	echo '<br /><br />'.$var;
}
else
{
	// The page didn't exist.

	echo 'Not Exists';
}

include "includes/footer.php";

/* END OF FILE */

PHP:

Give this a try. I hope it works first go, as I haven't tested the syntax.

For preventing duplicates:

INSERT INTO `table` (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY c=c+1;

Code (markup):

 

Thanks for your help ze0xify...

Parse error: syntax error, unexpected T_EXIT (Line 5)

I entered the correct details...

 

Whoops... haha. I forgot a ) bracket at the end of line 5:

if(!$link = mysql_connect('localhost', 'username', 'password'))

Code (markup):