stupid mysql question

**Chaldoray** · 10-22-2003, 10:58 PM

For example, this is how you call everything in mysql, the host, db name, etc...

$user="chaldoray"; // MySQL login name
$pass="ray"; // MySQL password
$hostname="localhost"; // MySQL server location
$dbase='schaldoray_db'; // MySQL database name
$connection = mysql_connect("$hostname" , "$user" , "$pass");
$db = mysql_select_db($dbase , $connection);

**mrthumbs** · 10-22-2003, 10:59 PM

Originally posted by Chaldoray
For example, this is how you call everything in mysql, the host, db name, etc...

$user="chaldoray"; // MySQL login name
$pass="ray"; // MySQL password
$hostname="localhost"; // MySQL server location
$dbase='schaldoray_db'; // MySQL database name
$connection = mysql_connect("$hostname" , "$user" , "$pass");
$db = mysql_select_db($dbase , $connection);

**Chaldoray** · 10-22-2003, 11:00 PM

Originally posted by mrthumbs

Confused? Hit me up on icq at 237842929

**broke** · 10-22-2003, 11:00 PM

Originally posted by mrthumbs

**Penthouse_mike** · 10-22-2003, 11:03 PM

mrthumbs,

my php/mysql is a bit rusty, but i think you are looking for something like this.
if $name has a value, a where clause is appended to the query

PHP Code:


$q = "SELECT * from table_name";

if ($name) {
$q .= "where name = '$name' ";
}

mysql_query($q);

**myneid** · 10-22-2003, 11:03 PM

you are gonna have to do it in whatever programming language you are using.

you can use a wildcard which happens to be % but you must use a different sql statement

select * from table where name = '$name'

select * from table where name like '%'

you can always do

select * from table where name = '$name%'

which if you pass it a name it will grab everything starting with that name.

but to really grab everything you should just do

select * from table;

**mrthumbs** · 10-22-2003, 11:05 PM

Originally posted by Chaldoray

Confused? Hit me up on icq at 237842929

i know how to connect to a database. Thanks tho.

I need the same results from:
" select * from table where name = 'what do i put here?' "

as i get from:
" select * from table "

There must be a wildcard character..

**jwerd** · 10-22-2003, 11:06 PM

Simple:

PHP Code:

if($whatver hahahaha "") {
$query = "select *from whatever";
$result = mysql_query($result);
} else if($query != "") {
$query = "select *from whatever where whatever = $whatever";
$result = mysql_query($query);
}

Basically what this is doing is seeing if the var $whatever doesn't contian anything... then if it doesn't it queries the database to have it show everything, but if it does it contain, then it searches for that...

If that doesn't work, hit me up on ICQ!

**mrthumbs** · 10-22-2003, 11:08 PM

Originally posted by Penthouse_mike
mrthumbs,

my php/mysql is a bit rusty, but i think you are looking for something like this.
if $name has a value, a where clause is appended to the query

PHP Code:

$q = "SELECT * from table_name"; if ($name) { $q .= "where name = '$name' "; } mysql_query($q);

yup.. but then i would need to modify a shitload of code instead
of simply replacing a static name into a var. Appreciate it tho and if theres no other option ill just have to start cut and pasting..

**Penthouse_mike** · 10-22-2003, 11:11 PM

np. hope it works out mrthumbs

**4Pics** · 10-22-2003, 11:15 PM

That code posted should work fine.

I don't think there is another way to do it though.

Post your question here

http://forums.devshed.com/forumdisplay.php?forumid=5

Someone should answer within 20mins.

**JSA Matt** · 10-22-2003, 11:15 PM

Give this a shot...

PHP Code:

SELECT * FROM table WHERE name REGEXP "^$name";

Off to bed

**mrthumbs** · 10-22-2003, 11:26 PM

Originally posted by JSA Matt
Give this a shot...

SELECT * FROM table WHERE name REGEXP "^$name";

Off to bed

Dude.. it worked!!!! Thanks.. you saved me a lot of time!!!

Hit me up if you want something in return 160272407 .

Thanks everybody for helping, gnite!

**margarita** · 10-22-2003, 11:56 PM

I've never tried to use regexp in database, but it must be damned slow, especially on big tables, isn't it ?

**Mr. Porno King** · 10-22-2003, 11:59 PM

SELECT * FROM table WHERE name REGEXP "^$name";

Yeah.. that'll work but..

SELECT * FROM table WHERE name LIKE "$name%";

is faster.

**Samoan** · 10-23-2003, 12:38 AM

Would this work?

SELECT * FROM db WHERE name like '%$name%'

**mrthumbs** · 10-23-2003, 12:43 AM

Originally posted by Samoan
Would this work?

SELECT * FROM db WHERE name like '%$name%'

nope.. % and %% no worky.. only the REGEXP works for me

getting some sleep!!

**Alky** · 10-23-2003, 12:49 AM

if you need help, contact me... icq 9287771

**Rivux** · 10-23-2003, 12:56 AM

You should do all of your logic statements in php and not in the queries. If you want a select all when there is no value, then test for the value BEFORE going into the select statement. A bit of pseudo code below as an exampel

If value is empty
myquery = "select * from table"
else
myquery = "select * from table where column = value
end if

You can then run myquery thru the database with no problems. Also, NEVER, NEVER use * when returning all columns, it slows querie down by up to 60%. List the columns you want to return don't use *. Yes it takes longer to type, but it will save you tonnes of cpu cycles and resources.

**Alky** · 10-23-2003, 01:02 AM

Originally posted by Rivux
You should do all of your logic statements in php and not in the queries. If you want a select all when there is no value, then test for the value BEFORE going into the select statement. A bit of pseudo code below as an exampel

If value is empty
myquery = "select * from table"
else
myquery = "select * from table where column = value
end if

You can then run myquery thru the database with no problems. Also, NEVER, NEVER use * when returning all columns, it slows querie down by up to 60%. List the columns you want to return don't use *. Yes it takes longer to type, but it will save you tonnes of cpu cycles and resources.

1.) Nothing wrong with doing mysql_query's inside if's.. just personal prefrences really. might look neater your way, thats all.

2.) using * shouldnt hurt as long as you set your indexes up correctly.

**Rivux** · 10-23-2003, 01:07 AM

Originally posted by Alky

1.) Nothing wrong with doing mysql_query's inside if's.. just personal prefrences really. might look neater your way, thats all.

2.) using * shouldnt hurt as long as you set your indexes up correctly.

1)The issue wasnt with doing IFs inside of mysql_query calls, the issue was with using LIKE and REGEXP just because you cant be bothered using an IF statement.

2)Yes it does hurt, regardless of whether indexes are setup properly or not at all. By using * you are forcing the DB to do a lookup of the columns in the table before it can return them, this is especially painful on joins. By specifying the columns, you are bypassing this step and the DB can return your columns without doing the lookup.

**Rivux** · 10-23-2003, 01:21 AM

One other reason not to use Select *, by not specifying the columns, you are returning columns that you do not need(unless you need all of them, which is actually rare), this means you are passing more data(bandwidth) back and forth between the DB Engine and the calling language.

And probably most importantly, it's a lazy habit, and lazy habits have a way of biting you when you least expect it. Also makes the code look sloppy which increases programming/updating time. But it won't of course, if you are some 1337 hacker who enjoys writing cryptic, uneditable code.

**JSA Matt** · 10-23-2003, 10:18 AM

You guys are insane. He was looking for a quick fix to a minor problem. This has nothing to do with indexes or using * instead of the column names. How do you know this is even his actual code and not just an example?

As far as you guys saying the process will be too heavy or slow, a properly setup mysql database can handle more than any of you could ever throw at it and that's a fact.

**JDog** · 10-23-2003, 10:24 AM

Originally posted by mrthumbs

yup.. but then i would need to modify a shitload of code instead
of simply replacing a static name into a var. Appreciate it tho and if theres no other option ill just have to start cut and pasting..

here try this

PHP Code:

if(!$name) { $name = '%'; }

put that up top!

jDoG

**DrGuile** · 10-23-2003, 10:30 AM

Originally posted by JSA Matt
As far as you guys saying the process will be too heavy or slow, a properly setup mysql database can handle more than any of you could ever throw at it and that's a fact.

Ahahaha, you should try working on real projects.

You can bring any MySQL database to its knee.

And the mysql setup isnt the problem, its sloppy code.

**JSA Matt** · 10-23-2003, 10:34 AM

Originally posted by DrGuile
You can bring any MySQL database to its knee.

Wow.. really? ::we need a sarcastic smilie

Like I said before.. a PROPERLY SETUP DATABASE can handle more than any of you could ever throw at it. Well, if it's on a good server..

**JDog** · 10-23-2003, 10:44 AM

Originally posted by DrGuile

Ahahaha, you should try working on real projects.

You can bring any MySQL database to its knee.

And the mysql setup isnt the problem, its sloppy code.

This is very true! It is code & it is the setup! If their not done right - it's going further than it's knees!

jDoG

stupid mysql question

stupid mysql question

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment