WHERE Statement

[X96 Web Design]

I have a database with a whole bunch of columns, for a search thing I'm building.

I can recall data from the DB, but I can't get it to work with variables.

Here's where I have trouble:

$keywords = $_GET['query'];
$topic = mysql_query("SELECT keywords FROM webindex WHERE keywords=$keywords");

I think it's the variable in the WHERE statement.

Am I doing something wrong?

Thanks,
X96

[Schmoopy]

Add ticks (`) around column names and enclose strings with quotes, like so:



$keywords = $_GET['query'];
$topic = mysql_query("SELECT `keywords` FROM webindex WHERE keywords='$keywords'");


Should fix it.

[X96 Web Design]

Nope - it just displays the entire table... not filtering...

Here's my entire PHP code:


<?php
// All Code (C) 2009 X96 Web Design
if(isset($_GET['n'])) {
$con = mysql_connect("localhost","myuser","mypass");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("index", $con);
$get = $_GET['n'];
$results = mysql_query("SELECT * FROM webindex");
$topic = mysql_query("SELECT `keywords` FROM webindex WHERE keywords='$get'");
while($row = mysql_fetch_array($results)) {
if(isset($_GET['n'])){
echo '<h2>Showing Results for <em>'.$_GET['n'].'</em></h2>';
echo '<p class="result"><a href="'.$row['url'].'" target="shared">'.$row['title'].'</a><br/><span><span>Indexed on '.$row['added'].'</span><br/>'.$row['description'].'</span><br/><span class="url">'.$row['url'].'</span></p>';
}
else {
echo '<h1>No Pages Found!</h1><p>No results could be found for the keyword(s) you entered.</p>';
}
}
mysql_close($con);
}
elseif(isset($_GET['n']) == null) {
echo 'Please enter a keyword into the search form.';
}
else {
echo 'Hacker.';
}

No matter what keywords are entered, it still displays all of the table data...

[Schmoopy]

Well it's because you're using the wrong query, you're getting the array from



$results = mysql_query("SELECT * FROM webindex");


So all rows are being selected from webindex, then you use this a bit later on:



while($row = mysql_fetch_array($results))


So of course the whole table is being echoed, you need to change the query to $topic:



while($row = mysql_fetch_array($topic))


Maybe I'm wrong, but no matter - Once you've defined the $topic variable you don't use it anywhere else in your code, so this will be the problem.

Make the above alteration and see what happens.

Since you'll still need the $results variable, due to you echoing out all those columns, like url etc, but I don't know where in the code you want to use the keyword.

[X96 Web Design]

It didn't work... :( But I scrapped my project, and am working on something else... :)

Thanks for your help!

// X \\

[bluewalrus]

This helped me but with some mods...


SELECT column FROM table WHERE column='$variable' and diffcolmn is not null


this is not using mysql though....also i have this enclosed in php tags to render it differntly which you'll need if your trying to process it.

[X96 Web Design]

I get this:


Parse error: syntax error, unexpected T_STRING in /home/public_html/members.php on line 17

All the column names are right...

Are you sure it's not SQL?

// X96 \\

[X96 Web Design]

Never Mind - I got it... I used this:

$sessionuser = $_SESSION['username'];
$sql = mysql_query("SELECT `column1` FROM `table` WHERE `column2` = '$sessionuser'");
$row = mysql_fetch_row($sql);
and then echoed it:

echo $row[0];
Problem solved.

// X96 \\

[X96 Web Design]

Now... How do I get it to echo all of the table contents instead of just the first row? It'll show me the data from the first row, but that's it...

Thanks.

[Schmoopy]

$sql = mysql_query("SELECT * FROM table");

while($row = mysql_fetch_array($sql)) { // We'll say there's a field called name

echo 'Hello, in this record the person\'s name is: ' . $row['name'];

}



Then add in relevant fields.

Or if this is happening after the query has completed then you can use:



foreach ($row as $record) {
echo $record . '<br />';
}

[X96 Web Design]

Great... It works - except, it shows 8 entries for each row! So I have 16 boxes for 2 rows... ???

Thanks for the base code Schmoopy!

// X96 \\

[Schmoopy]

What fields do you have in your database?

You put that after the row, like:


echo $row['email'];

Are you saying it's duplicating the same row?

Can you show me some code / outputs?

[X96 Web Design]

Here's a couple screenshots:

http://x96webdesign.uuuq.com/xtra/repeating_rows/

// X96 \\

[Schmoopy]

Ok, and the code?

[X96 Web Design]

Just added it to the page I linked to...

// X \\

[Schmoopy]

Ah you've combined the two, you're getting it from the database, then going through that array in another loop...

Change it to:



$sql = mysql_query("SELECT * FROM notes");
while($row = mysql_fetch_array($sql)) {
// Code here (echo '<div></div>' etc...)
}


Just a quick side note, it's more efficient to not output HTML markup in an echo, so instead of:



<?php
if($var) {
echo "<div>more html in here</div>";
}
?>


You should put:




<?php
if($var) {
?>
<div>more html in here</div>
<?php
}
?>


so you go in and out of PHP. This should make your page load faster.

Anyways, hope that fixes it.

Edit: Better explanation for why it repeats 4 times:

You're querying the database for the fields and then putting that into the $row array. You have 4 fields so $row contains 4 items.

So it's going through $row[0], output all the code in that record, then going to $row[1] -> $row[2] -> $row[3]

Etc...

Hope you understand that.

[X96 Web Design]

Okay... Thanks Schmoopy! It works perfectly. And I edited the code to be in and out of PHP.

Cheers,
X96

[X96 Web Design]

Here's another question - deleting table rows...

I want to delete a table row by using GET and the ID equals the ID for the row to be deleted...

I'm using this, but It won't work:

if($_GET['delete'] == $unique) {
mysql_query("DELETE FROM `notes` WHERE `owner` = '$sessionuser' AND `id` = '$delete'");
}
$delete = $_GET['delete'];
$sessionuser = $_SESSION['username']
$unique = $row[3];

Wow - this thread has changed topics about 5 times! :)

Thanks,
X96

[Schmoopy]

Don't use ticks (`) for the table name, you've got it right for the column names but the table name doesn't need ticks:



"DELETE FROM notes WHERE `owner` = '$sessionuser' AND `id` = '$delete'"


See if that works.

[X96 Web Design]

Nope... Nothing is deleted...

Is there something wrong with all the variables? Even I get confused with all the variables calling variables... and I wrote it!

// X96 \\

[Schmoopy]

This is probably obvious and you put them in just to show where you got them from. But the variables need to be before the if statement hehe :p. I'm sure that isn't the problem though.

Try doing
var_dump($variable) on the variables to see what it comes out with. Make sure all the variables are correct.