How to use array_rand() or shuffle() functions in php mysql

Question

I have the following working code to output the states and output the relatd cities under each state. However, I want to shuffle or randomize the cities in each state. For example, if the State is California then I want to shuffle or randomize the relate outputted cities under it. I tried to use different methods for this such as ORDER BY RAND(), implode() and so on but didn't get the correct result.

    $get_result = "select DISTINCT state_name, city_name from country WHERE country_name='$select_country' ORDER BY state_name ASC";
    $result = array();
    $run = mysqli_query($dbconfig, $get_result);

    while ($row=mysqli_fetch_array($run)) {
        $state_name = $row['state_name'];
        $city_name = $row['city_name'];

        if (!isset($result[$state_name])) {
            echo '<p>' . $state_name . '</p>';
            $result[$state_name] = true;
        }

        echo '<p style="margin-left: 10px;"><a href="http://examplesite.com/'.$city_name.'">'. $city_name .'</a></p>';
     }

Any help on this matter would be greatly appreciated.

WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST or $_GET data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented May 26, 2016 at 17:00

nospor · Accepted Answer · 2016-05-26 20:30:18Z

1

//first you need to fetch all data
$states = array();
while ($row=mysqli_fetch_array($run)){
    $state_name = $row['state_name'];
    $city_name = $row['city_name'];
    $city_with_dashes = $row['city_name_with_dash']; 
    if (!isset($states[$state_name])) {
        $states[$state_name] = array(
            'name' => $state_name,
            'cities' => array()
        );
    }
    //add city to state
    $states[$state_name]['cities'][] = array(
        'name' => $city_name,
        'dashes' => $city_with_dashes
    );
}

//now you can iterate by this data
foreach ($states as $state) {
    echo '<p>' . $state['name'] . '</p>';
    //shuffle state cities
    shuffle($state['cities']);

    //and display cities
    foreach ($state['cities'] as $city) {
        echo '<p style="margin-left: 10px;"><a href="http://examplesite.com/'.$city['dashes'].'">'. $city['name'] .'</a></p>';            
    }
}

edited May 26, 2016 at 20:30

answered May 26, 2016 at 17:04

nospor

4,2301 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

E. Edgo Over a year ago

Great answer, I tried your code and it works. However, I still have one issue which is when I add a different value to the city linking URL all the city URLs point to only one city. For example when I use the '.$city_with_dashes.' variable in this URL value then all output city links point to only one city: echo '<p style="margin-left: 10px;"><a href="examplesite.com/'.$city_with_dashes.'">'. $city .'</a></p>'; I have declared this variable in while loop as: $city_with_dashes = $row['city_name_with_dash']; I have included city_name_with_dash in MySQL SELECT statement as well.

nospor Over a year ago

In that case you have to add to city array not only name but also dashes. You can do it by array. I've updated my code in answer and there is now what you need (city name and city name with dash) :)

splash58 · Accepted Answer · 2016-05-26 21:07:39Z

1

Change your query adding group_concat function and then take random value from arrays of cities

$get_result = "select state_name, 
                      group_concat(city_name) city_name
                 from country 
                 WHERE country_name='$select_country' 
                 group by state_name 
                 ORDER BY state_name ASC";

$result = array();
$run = mysqli_query($dbconfig, $get_result);
while ($row=mysqli_fetch_array($run)){
    $state_name = $row['state_name'];
    $city_names = shuffle(explode(',', $row['city_name']));
    echo '<p>' . $state_name . '</p>';
    foreach ($city_names as $city)
        echo '<p style="margin-left: 10px;">
              <a href="http://examplesite.com/'.$city_name.'">'.
              $city_name[array_rand($city_name)] .'</a></p>';

}

edited May 26, 2016 at 21:07

answered May 26, 2016 at 17:04

splash58

26.2k3 gold badges25 silver badges35 bronze badges

2 Comments

E. Edgo Over a year ago

Thank you for your reply. I tried your code and it outputs only one random city per state. I am sorry if my original question was not that clear. I want to output all the related cities under states and shuffle or randomize them. For ex: if California has 25 cities, I want to list all of them under California and shuffle (randomize the cities not to have the same listing order) these cities every time when I refresh the page.

splash58 Over a year ago

the code reques minimum changes because $city_names already contains list of all cities

Collectives™ on Stack Overflow

How to use array_rand() or shuffle() functions in php mysql

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related