Bit of confusion - regards to mysql queries
Moderator: General Moderators
Bit of confusion - regards to mysql queries
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.
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.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Bit of confusion - regards to mysql queries
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
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'];
}
?>“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Bit of confusion - regards to mysql queries
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 ?
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 ?
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Bit of confusion - regards to mysql queries
It prevents SQL injection attacks. You should never trust data that comes from users (post, get, etc...). Read here:Rippie wrote:Could you help with explaining why use .mysql_real_escape_string($something_else). ??
http://us2.php.net/manual/en/security.d ... ection.php
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: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 ?
Code: Select all
$user_query
$group_query
$post_query
//etc...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 datamysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: Bit of confusion - regards to mysql queries
Just had a play around.
i cant get following to work:
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
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 />";
}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
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 />";
}- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Bit of confusion - regards to mysql queries
http://w3schools.com/sql/sql_join_inner.asp
should be without "WHERE WhoCanDoWhat.productcode = $productcode". See url for reference.
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)
Code: Select all
<?php
$query = @mysql_query("SELECT * FROM people
INNER JOIN WhoCanDoWhat ON people.resourceid = WhoCanDoWhat.resourceid
WHERE WhoCanDoWhat.productcode = $productcode ");
?>Code: Select all
<?php
$result = mysql_query("SELECT family.Position, food.Meal "."FROM family, food "."WHERE family.Position = food.Position") or die(mysql_error());
?>“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Bit of confusion - regards to mysql queries
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.
It list EVERYTHING in the WhoCanDoWhat table. i want it to list only people who has a certain productcode in WhoCanDoWhat table.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Bit of confusion - regards to mysql queries
Yes, it seems you can use a WHERE statement where (no pun) you did.
Your query would be something like :
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'];
}
?>“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Bit of confusion - regards to mysql queries
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.
$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.