search script issue

Forum Moderators: coopster

Message Too Old, No Replies

search script issue

RogueDogg

8:04 pm on May 30, 2006 (gmt 0)

<?
$db = mysql_connect("localhost", "username", "password");
mysql_select_db("databasename", $db);
$query = "SELECT clients.firstname, clients.lastname, clients.phone 
FROM clients WHERE name LIKE '%".$name."%'";
$result = mysql_query($query);
while ($record = mysql_fetch_assoc($result)){
while (list($fieldname, $fieldvalue) = each ($record)){
echo $fieldname.": <b>".$fieldvalue."</b><br>";
}
echo "<br>";
}
?>

This is the error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/cashflow/public_html/search/results.php on line 15

How can this be?

RogueDogg

3:24 am on May 31, 2006 (gmt 0)

Ok it seems to be working to the point where I don't get any errors when I search for a name and I get good results. I do see the become a member button and I click on it and don't get any errors, I do however notice it's not removing the user from the old db but it is adding the them to the new db even though the email information it's adding is wrong. So....we're getting there. I really do appreciate ALL that you are doing for me. I understand if you need to go, again thank you for everything and hopefully because we're so close we can get this 100%... THANKS A MILLION so far...

eelixduppy

3:28 am on May 31, 2006 (gmt 0)

Real quick before i leave. Change the one query that looks like this:
$query = "INSERT INTO full_members VALUES ('$_POST[fname]', '$_POST[lname]', '$_POST[email]', '$_POST[phone]', '$_POST[phone2]', '$_POST[address]', '$_POST[city]', '$_POST[state]', '$_POST[zip]', '$_POST[enter_date]')";

to this:
$query = "INSERT INTO full_members (First, Last, email, phone, phone2, Address, City, State, Zip, enter_date) VALUES ('$_POST[fname]', '$_POST[lname]', '$_POST[email]', '$_POST[phone]', '$_POST[phone2]', '$_POST[address]', '$_POST[city]', '$_POST[state]', '$_POST[zip]', '$_POST[enter_date]')";

This should fix how you data is being entered into the table, that is, as long as this table is identical to the other table with column names. As for the delete, i would echo the query to see what is going on with it. Try maybe entering it in manually to see if it works. Gooc luck!

[edited by: jatar_k at 4:06 am (utc) on May 31, 2006]
[edit reason] fixed sidescroll [/edit]

RogueDogg

4:30 am on May 31, 2006 (gmt 0)

OK...we have a winner! Here's the code we came up with that is doing what it's suppose to do:

^{<?php
$name = $_POST["name"];
error_reporting(E_ALL);
if(isset($_POST["add_member"]))
{activate();}
else
{show_users();}

function activate()
{
$backlink = "<br><br><a href='search.html'>Do another Search CLICK HERE</a>";
$link = mysql_connect("localhost", "dbuser", "dbpassword");
mysql_select_db("dbname", $link);
$_POST = array_map("mysql_real_escape_string",$_POST);}

$query = "INSERT INTO full_members (fname,lname,email,phone,phone2,address,city,state,zip,enterdate) VALUES ('$_POST[fname]', '$_POST[lname]', '$_POST[email]', '$_POST[phone]', '$_POST[phone2]', '$_POST[address]', '$_POST[city]', '$_POST[state]', '$_POST[zip]', '$_POST[enter_date]')";
$result = mysql_query($query,$link) or die("ADD query not working<br>".mysql_error());
if($result){ echo "Added to database successfully!<br>"; }
else { echo "Added unsuccessfully<br>"; }
delete_member($_POST["email"]);
echo "<br>Thank you for becoming a FULL Member!<br>";
echo "$backlink";
mysql_close($link);
}

while ($row=mysql_fetch_array($result)) {
?>

<tr>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><? echo $row["First"]." ".$row["Last"];?></font></td>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><? echo $row["phone"];?></font></td>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><? echo $row["phone2"];?></font></td>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><? echo $row["Address"];?></font></td>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><? echo $row["City"];?></font></td>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><? echo $row["State"];?></font></td>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><? echo $row["Zip"];?></font></td>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><a href="mailto:<? echo $row["email"];?>">E-mail</a></font></td>
<td><font size="-2" face="Verdana, Arial, Helvetica, sans-serif"><? echo $row["enterdate"];?></font></td>
<td><form action="<? echo $_SERVER['PHP_SELF'];?>" method="post">
<input type="hidden" name="fname" value="<? echo $row["First"];?>" />
<input type="hidden" name="lname" value="<? echo $row["Last"];?>" />

<?
}
echo "</table>";
echo "<br>$backlink<br>";
}
?>

The only thing I don't like so far is that I had to declare the "$backlink" like 4 times in order for it to show up under all circumstances ( i.e. back search value, successful search value, adding/deleting of user from the 2 db's ). I guess if I was smart I would make a php file of it and just do an include maybe? or sometype of global variable? I dunno but anyways it's working, now the trick is to get it to work with the actual db's and not my test db's. :-(

[edited by: jatar_k at 4:38 am (utc) on May 31, 2006]
[edit reason] fixed sidescroll [/edit]

RogueDogg

8:02 am on May 31, 2006 (gmt 0)

Ok new issue, I got it working with the actual client database I will be working with. One thing however, the other software that will interface with this needs a "username" defined in this script, and I'm not sure how I'd define one seems how it's not pulling that info from the previous database ( Like Firstname, Lastname et...) Could I just add another variable called $username and use the FirstName field to populate it? Does that make any sense?

eelixduppy

10:18 am on May 31, 2006 (gmt 0)

For the back link, I would leave them where they are. Two of them you exit the script after you echo it. It looks find to me. As for the username, are you going to require login in order to register an account? And why would you use the Firstname as the username? I would make another column for the username, and password.

RogueDogg

6:43 pm on May 31, 2006 (gmt 0)

Well the database I'm pulling from and deleting after I "activate" is read from a csv file, so any additions would have to be made for every single entry ( which sometimes there's thousands ). Should I just add a column to the db and call it CUID ( ie customerID ) and have it auto_increment and then just pull the data from that? I think that might work...right? No at this point there will be no need for them to log in but eventually they will so setting it up now will be in my best interest I think.

*EDIT* Nevermind I already have a column with CUID and it's auto_increment...DOH! I have a question though, I am having a huge brain fart here....I want to delete every entry in the table but keep the columns...so in essence start over with a clean db & table...I forgot how to do this, actually I never really learned how to do this, just to delete everything ( including columns...which I don't want to do ). Know what I mean? <--do I keep typing that...hehe

*EDIT 2* WOW I make more edits than anyone I think, anyways, I fount a way in mysql to copy the table structure with no data so I will make a copy of it and delete the old one. I'm good I think...for now

eelixduppy

7:48 pm on May 31, 2006 (gmt 0)

>>>I am having a huge brain fart here
It happens to the best of us ;)

>>>delete every entry in the table but keep the columns
Just run this query:
delete from table_name;

jatar_k

7:51 pm on May 31, 2006 (gmt 0)

you also might want to look at truncate

[dev.mysql.com...]

this should reset auto increment values as well

eelixduppy

7:53 pm on May 31, 2006 (gmt 0)

Thanks for that jatar, I didn't know that.

RogueDogg

8:05 pm on May 31, 2006 (gmt 0)

WOW I am making leaps and bounds thanks to all your help ( THANK YOU VERY MUCH ). I am learning a ton, I've always been good at changing things up and making them work, I've just never been good at creating from scratch ( too impatient I guess )...I got it so that it's using the CUID from db1 as the username in db2, it's not the best solution but it works and it should be unique enough, although now that I think about it, whenever I empty db1 and then fill it up again it might auto_increment from the beginning again, in essecence creating duplicate usernames? maybe...hmmm I'll have to test that.

Also I should be able to pass that CUID/Username to db2 without having to display it after the search from db1 right? Keep it hidden?

eelixduppy

8:11 pm on May 31, 2006 (gmt 0)

You can keep it hidden using input (type=hidden) tags, just don't echo it to the browser so that it is viewed on the page.

jatar_k

8:14 pm on May 31, 2006 (gmt 0)

just use DELETE when you empty db1 and that will not reset the auto increment field, there by keeping your usernames unique

you could also select the highest value from db2 and start at that number + 1 and use a counter in your insert loop

or you could just set the auto increment on db2 and not use that value on insert, let it take car eof the incrementing

cats should be nervous, there's alot of ways to skin 'em ;)

RogueDogg

5:42 pm on Jun 1, 2006 (gmt 0)

Thanks jatar_k, I used your last example, made a auto_increment column on db1 and used that as the username on db2 and left the auto_increment on db2 alone, will use that for customerID purposes. Works great.

Now that I've got that complete, of course my boss wants to change this and that and add more to it....argh...the fun never stops.

Thanks again to you all who helped me, I'm sure I'll need more of it in the future... :0)

eelixduppy

6:24 pm on Jun 1, 2006 (gmt 0)

lol...You're welcome

This 44 message thread spans 2 pages: 44