CODEWITHSUNDEEP
phppdo
LJSven
LJSven

Reputation: 147

PHP SQL / multible select request / slow

I have 2 requests on a SQL database. It takes really long to load the whole script / page. Is there a better (faster) way? My code looks like this

    $abfrage = "SELECT * FROM table_a WHERE state = '0' ORDER BY EDIT DESC";
        $stmt = $pdo->query($abfrage);

        foreach($stmt as $data) { ?>

$usern = $data['user_name'];
$stmt = $pdo->query("SELECT * FROM tabl_b WHERE user_name = '$usern' ORDER BY follower DESC Limit 1");
$stmt->execute();
$pl = $stmt->fetch();

<?php echo $data['ID'];?>
        <?php echo $data['ID'];?>

        <?php if ($data['company'] == '') { ?>
        <a href="https://open.spotify.com/user/<?php echo $data['user_name'];?>" target="_blank"><?php if ($data['state'] == '3') { echo "<span style=\"color:#0000FF\";>".$data['user_name']."</span>"; } else { echo $data['user_name'];}?></a>
        <?php } else {?>

        <a href="https://open.spotify.com/user/<?php echo $data['user_name'];?>" target="_blank"><?php if ($data['state'] == '3') { echo "<span style=\"color:#0000FF\";>".$data['company']."</span>"; } else { echo $data['company'];}?></a>

        <?php } ?>

        <?php echo $data['PL_read'];?>
        <?php echo $data['PL_total'];?>
        <?php echo $data['country'];?>
        <?php echo $data['EDIT'];?>
    <?php echo $pl['follower'];?>
        <?php } ?>

I have a table around th echo data. I would be great if someone can give me a tip.

Upvotes: 1

Views: 117

Answers (2)

Vinod Kirte
Vinod Kirte

Reputation: 187

  1. When it comes for loading time then we should only fetch those column from table which are required, So in first query we can fetch user_name, ID, company, PL_read, PL_total, country, EDIT

  2. Since the query which are running inside loop will also take time, so to avoid this use below code 

        $dataArray = array();
    $userName = NULL;
    $abfrage = "SELECT ID, user_name, company, PL_read, PL_total, country, EDIT FROM table_a WHERE state = '0' ORDER BY EDIT DESC";
    $stmt = $pdo->query($abfrage);

    foreach($stmt as $data) {
        //creating a string
        if (is_null($userName))
        {
            $userName = "'" . $data['user_name'] . "'";
        }
        else
        {
            $userName .= ",'" . $data['user_name'] . "'";
        }
        $dataArray[$userName] = $data;
    }

    $stmt = $pdo->query("SELECT follower, user_name FROM tabl_b WHERE      user_name IN ($userName) ORDER BY follower");

    foreach($stmt as $data) { 
        if(isset($dataArray[$userName]))
        {
            $dataArray[$userName]['follower'] = $data['follower'];
        }
        else
        {
            $dataArray[$userName]['follower'] = 'N/A';
        }
    }

    foreach($dataArray as $data) { 
            echo $data['ID'];?>
            <?php echo $data['ID'];?>

            <?php if ($data['company'] == '') { ?>
                <a href="https://open.spotify.com/user/<?php echo $data['user_name'];?>" target="_blank"><?php if ($data['state'] == '3') { echo "<span style=\"color:#0000FF\";>".$data['user_name']."</span>"; } else { echo $data['user_name'];}?></a>
            <?php } else {?>
                <a href="https://open.spotify.com/user/<?php echo $data['user_name'];?>" target="_blank"><?php if ($data['state'] == '3') { echo "<span style=\"color:#0000FF\";>".$data['company']."</span>"; } else { echo $data['company'];}?></a>

            <?php } ?>

            <?php echo $data['PL_read'];?>
            <?php echo $data['PL_total'];?>
            <?php echo $data['country'];?>
            <?php echo $data['EDIT'];?>
            <?php echo $pl['follower'];?>
 <?php } ?>

Upvotes: 0

codtex
codtex

Reputation: 6548

First if tables have relation I suggest you to take the data in a single call with join.

SELECT * FROM table_a a
LEFT JOIN tabl_b b ON a.user_name = b.user_name
WHERE a.state = '0'

Second for speeding up the things you can put INDEX on the two fields that you are using in the WHERE clause - state, user_name

ALTER TABLE `table_a` ADD INDEX `state` (`state`)
ALTER TABLE `tabl_b` ADD INDEX `user_name` (`user_name`)

Upvotes: 2

Related Questions