Decreasing connections to the database

Asked

Viewed 99 times

1

Well, I know that each connection requires a bit of server memory (the exact amount I don’t know), and I know that several connections can weigh my site, thinking about it I would like to know if it has how to unify the code:

    $proximo = $pdo->query("SELECT * FROM postagens WHERE ID > ".$pid." AND ID_user = ".$cid." ORDER BY ID DESC")->fetchAll();
if(!$proximo){
print_r($pdo->errorInfo());
} 
foreach ($proximo as $next){};

I use this code in a photo gallery, where I capture the current ID and create a link that redirects to the next post. It would be simple if I repeated the code again and changed the sign ">" to "<" and consequently the name of the variables so I would build a link to the previous post. But this would require two simultaneous connections to the BD, and these two connections even if they are useful also become useless, since they only serve to create two simple links. Could I use one unique connection to create these two links? Or in this case the only way is to keep both connections?

Talking about this same code I would like to know how to fix another "little problem" I’m having with it, as I said, this code creates a link that advances in the ID of the posts, the "little problem" that arose (what was expected), is that when reaching the end of the ID that correspond to the Id_user, an error arose saying that the ID does not exist, and really does not exist. You could then do some command where once you reach the last ID corresponding to the Id_user the ID 'zere' and return to the first ID, thus creating a kind of loop where you can proceed continuously?

php
  • 2

    You will make two queries, but only one connection. $Pdo is the connection, you will use the same for the two 'query'. Another detail, if it is only for link, you do not need foreach, and can keep LIMIT1 of the other answer, not to make the BD send unnecessary data. If you are going to link to several next and previous at once, then the solution given in the other question could be adjusted to take everything in one query, and split the result in PHP, but for that, you would have to be sure how you want the result.

  • Suppose then that I create 4 codes similar to that, changing only the variables, but keeping the $Pdo, the server will interpret as only a single connection?

  • The other two were just for show, so I could have a better purpose

  • 1

    Well, I tried it for an example of how to do the "loop" in the answer, but it would be nice for you to give more details when asking, because if I just wonder what you want to do, I run the risk of saying some nonsense that does not suit your specific case :)

  • Oops, messing you up again and still working with the same code, I would like to know how I can 'improve' the code. As I told you this connection there aims to build the link of the next post and the previous post. Only that is the following, all posts are registered in the same table, and not always these posts have media (image or video), which causes the following result "Array ( [0] => 00000 [1] => [2] => )", it would be simple to end it, but I want these posts without the media not to be listed... (CONTINUE)

  • (Continue), I want $next to list only ID’s where the "image" or "video" field is not empty. Could you do that I’m asking you? If you prefer I open another question and put all the codes so you can understand better

  • 1

    I think it would be an exaggeration a new question, just add the details in the WHERE (... AND video = "" AND image = """... and so on. Now, if you need a new question, put the code up to date and as clean as possible on the new question, so as not to confuse the staff.

Show 2 more comments

1 answer

2


To catch the first item when arriving at the last:

// Pegamos o próximo
$proximo = $pdo->query("SELECT * FROM postagens WHERE id > ".$pid." AND id_user = ".$cid." ORDER BY id LIMIT 1")->fetchAll();
if(!$proximo){
   // se nao tem proximo, pegamos o 1o
   $proximo = $pdo->query("SELECT * FROM postagens WHERE id_user = ".$cid." ORDER BY id LIMIT 1")->fetchAll();
}

To catch the previous, use the < in the first query, and add the DESC in both.

I get the impression you’re mixing the use of > and of DESC, but only seeing the real code to be sure. I added the LIMIT 1 Assuming you only want one "next" at a time.

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.