Page 1 of 1
Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 7:47 am
by Rippie
Hi everyone,
Kinda new to php, and could do with some clarification on a few things. as the title says it is in regards to mysql queries.
1. Variables:
$query = @mysql_query("SELECT vendor FROM vendor WHERE vendorcode = '".$vendors."' ");
$row = @mysql_fetch_array($query);
$rr = @mysql_num_rows($query);
$query, $row & $rr variables, can they be ANYTHING ? and if they can, is there an written/unwritten guide line in what terms you use ? Also if you want to count the rows and fetch the data, is it ok to have it as my example above ?
2. Mysql_query:
Why use @mysql_query and not just mysql_query ?
Why use the mysql_real_escape_string instead of just a normal variable ?
SELECT* FROM table WHERE something = '".mysql_real_escape_string($var)."'
3. How to:
If you got 3 tables, one for people, one for products and one that contain peopleid and productid. Last one to link people to products.
If i want the full name for those people who can do a specific product, how would i go around that ? Right now i can get resourceid printed to screen from the table that contains peopleid and productid. but i want to use those resourceid's and look up in people table that contain the full names of the people.
I believe people call it a many-to-many relation table.
Thank you for any help.
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 8:03 am
by social_experiment
1.Yes, the count and fetch queries should work. The url below covers variables.
http://www.php.net/manual/en/language.v ... basics.php
2.The @ 'squelches' the error that would be given if there was something wrong with the mysql_query (if you couldn't connect to the database, etc)
3. I'd say use INNER JOIN and connect the two tables with that
Code: Select all
<?php
$query = @mysql_query("SELECT * FROM table1 INNER JOIN table2 ON table1.foreignkey = table2.foreignkey ");
while ($value = @mysql_fetch_array($query)) {
echo $value['name'];
}
?>
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 8:30 am
by Rippie
Could you help with explaining why use .mysql_real_escape_string($something_else). ??
Also what happens if i have:
$query = mysql_query
$row = mysql_fetch_array
and further down
$query = mysql_query
$row = mysql_fetch_array
Would the second one overwrite the first one ?
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 9:32 am
by AbraCadaver
Rippie wrote:Could you help with explaining why use .mysql_real_escape_string($something_else). ??
It prevents SQL injection attacks. You should never trust data that comes from users (post, get, etc...). Read here:
http://us2.php.net/manual/en/security.d ... ection.php
Rippie wrote:
Also what happens if i have:
$query = mysql_query
$row = mysql_fetch_array
and further down
$query = mysql_query
$row = mysql_fetch_array
Would the second one overwrite the first one ?
Yes, and that is O.K. if you don't need the first set of variables anymore. If you do need both sets for whatever reason, then you can be more descriptive:
Code: Select all
$user_query
$group_query
$post_query
//etc...
As for variable naming, they can be anything that conforms to the rules that social_experiment posted a link for, however they should be descriptive and many times describe what they contain. Some examples:
Code: Select all
$conn = mysql_connect(); //$conn for connection
$query = "SELECT * FROM blah WHERE foo=bar"; //this is the actual the Query
$result = mysql_query($query); //this returns a "result" or result set
$row = mysql_fetch_array($result); //this returns one row of data
Also, I would not use @ especially when you are learning. There are ways to turn off errors when your app goes live, but during development you want to see them all.
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 9:38 am
by Rippie
Just had a play around.
i cant get following to work:
Code: Select all
$query = @mysql_query("SELECT * FROM people
INNER JOIN WhoCanDoWhat ON people.resourceid = WhoCanDoWhat.resourceid
WHERE WhoCanDoWhat.productcode = $productcode ");
while ($value = @mysql_fetch_array($query)) {
echo " name: ".$value['resourcename']." <br />";
}
Just a blank screen. Blank screen happend after i introduced the where command. Basiaclly i need it to only pick the people that has the productcode for a product in WhoCanDoWhat table.
My tables is like this:
PEOPLE:
ID - resourceid - resourcename
WhoCanDoWhat:
ID - resourceid - productcode
resourceid in people and whocandowhat will be the same.
Hope someone can help
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 9:55 am
by Rippie
Would this work for my purpose ?
Code: Select all
$result = mysql_query("SELECT family.Position, food.Meal "."FROM family, food "."WHERE family.Position = food.Position") or die(mysql_error());
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['Position']. " - ". $row['Meal'];
echo "<br />";
}
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 10:58 am
by social_experiment
http://w3schools.com/sql/sql_join_inner.asp
Code: Select all
<?php
$query = @mysql_query("SELECT * FROM people
INNER JOIN WhoCanDoWhat ON people.resourceid = WhoCanDoWhat.resourceid
WHERE WhoCanDoWhat.productcode = $productcode ");
?>
should be without "WHERE WhoCanDoWhat.productcode = $productcode". See url for reference.
Code: Select all
<?php
$result = mysql_query("SELECT family.Position, food.Meal "."FROM family, food "."WHERE family.Position = food.Position") or die(mysql_error());
?>
Im not sure if you can select from multiple tables at the same time (that's how im reading the query, i could be mistaken)
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 11:12 am
by Rippie
if you look here:
http://www.rippie.dk/techresource/testpage.php
It list EVERYTHING in the WhoCanDoWhat table. i want it to list only people who has a certain productcode in WhoCanDoWhat table.
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 11:19 am
by Rippie
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 11:23 am
by social_experiment
Yes, it seems you can use a WHERE statement where (no pun) you did.
Your query would be something like :
Code: Select all
<?php
$query = mysql_query("SELECT * FROM table1 INNER JOIN table2 ON table1.foreignKeyField = table2.foreignKeyField WHERE table2.fieldYouWantToTestAgainst = 'ValueYouWantToSelect' ");
while ($arrayQuery = mysql_fetch_array($query)) {
echo $arrayQuery['fieldYouWantToShow'];
}
?>
Re: Bit of confusion - regards to mysql queries
Posted: Thu Jan 28, 2010 4:14 pm
by Rippie
Got it working like this.
$query = @mysql_query("SELECT *
FROM people po
JOIN WhoCanDoWhat w ON po.resourceid = w.resourceid
JOIN products pr ON w.productcode = pr.productcode
WHERE pr.productcode = 'jss'");
while($row = mysql_fetch_array($query)){
echo $row['resourcename'] . " " . $row['product'] . "<br/>";
}
Thank you everyone.