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
http://publib.boulder.ibm.com/infocente ... nwhere.htm

this actually show we can use where

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.