Sql query problems

[bolfescu]

hy, i'm having a big problem, thats because i'm new in this, i have two qrys unfort doesnt work

if(isset($_GET['id'])){
    $id=$_GET['id'];

    $qry=mysql_query("SELECT *,replace(category, ',', ' ') as category FROM articles WHERE id='$id'", $con);
    $sql = mysql_query("select *,Total17,sum(q5) Total18,sum(aste3) Total19 from articles where category= how ?",$con);


    if(!$qry){die("Query Failed: ". mysql_error());
}
while($row=mysql_fetch_array($qry)){ `"

so the problems is that id i can get it with $_get, but the category row how?

example table 
id name name2 category
1    a    b     e
2    c    d     e
2    c    d     f

i want to sum all the rows where category = e(like id) if it is possible to make one query
the problems its that the query is limited at that id , but i trying to show two type in the page, one specific details from that ID and the total of the category(category dosnt have id)
thanksssss

[bolfescu]

          if(isset($_GET['id'])){
        $id=$_GET['id'];

$qry=mysql_query("SELECT *,replace(category, ',', ' ') as category FROM articles WHERE id='$id'", $con);
$qry1 = mysql_query("select *,Total13,sum(q5) Total18,sum(aste3) Total19 from articles where category='$cat'",$con);          



 if(!$qry){die("Query Failed: ". mysql_error());
 }
 while($row=mysql_fetch_array($qry)){ 

 $cat="".$row['category']."";


 something like this so i whant to make 1 first query and the seecond query needs to be based from a field from first query

[bolfescu]

          $current_date = date("d/m/Y");
          if(isset($_GET['id'])){
        $id=$_GET['id'];


$qry = mysql_query("select * from articles where id='$id' ",$con);       
$qr1 = mysql_query("select *,sum(q6) Total13 from articles where category='$cat2'",$con);




 if(!$qry){die("Query Failed: ". mysql_error());
 }
$row= mysql_fetch_assoc($qry);

$row1= mysql_fetch_assoc($qr1);
$cat2="".$row['category']."";

doesnt work .....

[Taywin]

You should update and switch to mysqli or PDO format... Besides, I see no need of select all (*) if you need only category, but I don't know your DB schema, so I am not going to make more comment on that.

Anyway, you need to obtain $qry and then check whether the query is successful. If so, retrieve the category from the row, and use that to get $qr1 inside the while loop. Currently, you attempt to retrieve it OUTSIDE the loop which could easily cause an error when $qry fails.

Also, you need to STOP the script if $qry failed. Currently, you let it go through and it will be an error because of the failure.

PS: I do not want to modify your script because it is using bad format already. So rather modify it, I try to explain how to fix your current issue...

[bolfescu]

i know its bad format, but i didnt understand...

[bolfescu]

the

$cat2="".$row['category']."";

should be extracted from qry and used to qry2 for where statement....

[Traevel]

Read Taywin's last part again:

I do not want to modify your script because it is using bad format already

If he gave you the answer before you modified the code to a proper mysqli or PDO format it would stay bad code. You might have your category issue fixed, but tomorrow something else will cause a problem, and the next day another, and so on.

His advice to fix the format of your code now while you're still working on the basics is one I fully agree with.

I suggest you rewrite your code from mysql syntax to mysqli syntax as the two are rather similar.

So replace mysql_query with mysqli_query and so on. Also, it would be safer to use prepared statements and not just put variables directly in the query. For a more in depth explanation on using mysqli you could read a tutorial.

From the tutorial:

$sql='INSERT INTO customers (firstname, lastname) VALUES (?,?)';

$firstname = 'John';
$lastname = 'Doe';

$stmt = $conn->prepare($sql);
if($stmt === false) {
  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
}

/* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */
$stmt->bind_param('ss',$firstname,$lastname);

/* Execute statement */
$stmt->execute();

See how that is different from what you have now. That is why an answer on how you have it now won't help you, or anyone else reading this post later looking for answers.

[bolfescu]

still not able to fix it, i've already tell that i'm new, anyway thanks

[Traevel]

If you're new you should be willing to learn and follow tutorials. What's the point in one of us converting your format for you? You're not saying you're having a problem with a small part of the conversion, but with everything. How can we ever help with that, teach you PHP from scratch?

Using mysqli you could try something like:

$id = 5; 

$sql = "SELECT id, cat FROM articles WHERE cat = (SELECT cat FROM articles WHERE id = ? LIMIT 1)";

$stmt = $conn->prepare($sql);

if($stmt === false) {
  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
}

$stmt->bind_param('i',$id);

$stmt->execute();

$stmt->bind_result($id, $cat);

while ($stmt->fetch()) {
  echo $id . ' = ' . $cat . '<br>';
}

$stmt->close();

Which is all taken from that exact same tutorial. It should print all id's and cat's of the articles that have the same category as the article with id number 5 (this includes 5 itself). If you don't have articles, or categories or unique id's it will go wrong.

Also, it needs error handling, if you keep it like this things will hit the fan at some point.