Get data from MySQL database table by specific field in url

Question

Im new to PHP MySQL. Im developing a songbook site.

I want to create tables alphabetically in separate pages for each alphabet.

When a user click an alphabet in the menu it will direct to this page site/publicsearch.php?browse=a

This is the database :

| ID  |  TITLE        | ARTIST | CATEGORY | ALPHABET |
+----------------------------------------------------+
|  1  | Amazing love  | XXXXXX | Love     |     a    |
|  2  | Above all     | XXXXXX | Worship  |     a    |
|  3  | BXXXX         | XXXXXX | Love     |     b    |
|  4  | BXXXX         | XXXXXX | Worship  |     b    |

I pull the above database table like this : It works fine.

   <?php
$servername = "localhost";
$username = "xxxxxxx";
$password = "xxxxxxxx";
$db_name = "xxxxxxxxxxx";

// Create connection
$conn = new mysqli($servername, $username, $password, $db_name);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

echo "Connected successfully";
exit();
}
    // Attempt select query execution

    $sql = "SELECT * FROM lyrics_a";

    if($result = mysqli_query($conn, $sql)){

        if(mysqli_num_rows($result) > 0){

            echo "<table class='table'>";

                echo "<tr>";

                    echo "<th>#</th>";

                    echo "<th>Title</th>";

                    echo "<th>artist</th>";

                    echo "<th>cateogry</th>";

                echo "</tr>";

            while($row = mysqli_fetch_array($result)){

                echo "<tr>";

                    echo "<td>" . $row['id'] . "</td>";

            echo "<td> <a href=publicsong.php?id=".$row['id'] . ">" . $row['eng_title'] . "</a> " . $row['tel_title'] . " </td>";

                    echo "<td>" . $row['artist'] . "</td>";

                    echo "<td>" . $row['category'] . "</td>";

                echo "</tr>";

            }

            echo "</table>";

            // Close result set

            mysqli_free_result($result);

        } else{

            echo "No records matching your query were found.";

        }

    } else{

        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);

    }



    // Close connection

    mysqli_close($link);

    ?>

I need separate alphabetical tables like this :

Alphabet "a" table results `site/publicsearch.php?browse=a` 

| ID  |  TITLE        | ARTIST | CATEGORY | ALPHABET |
+----------------------------------------------------+
|  1  | Amazing love  | XXXXXX | Love     |     a    |
|  2  | Above all     | XXXXXX | Worship  |     a    |

Alphabet "b" table results `site/publicsearch.php?browse=b` 

| ID  |  TITLE        | ARTIST | CATEGORY | ALPHABET |
+----------------------------------------------------+
|  3  | BXXXX         | XXXXXX | Love     |     b    |
|  4  | BXXXX         | XXXXXX | Worship  |     b    |

I tried the below code doesn't work.

<?php
$servername = "localhost";
$username = "xxxxxx";
$password = "xxxxxxx";
$db_name = "xxxxxxxxx";

// Create connection
$conn = new mysqli($servername, $username, $password, $db_name);
// Check connection
if ($conn->connect_error){
  die("Connection failed: " . $conn->connect_error);
} 
$alphabet = $_GET['alphabet'];
$id = mysqli_real_escape_string($conn,$id);
$query = "SELECT * FROM `lyrics` WHERE `alphabet`='" . $alphabet . "'";
$result = mysqli_query($conn,$query);

while($row = mysqli_fetch_array($result)) {
echo "<br><br>";
echo $row['id'];
echo $row['title'];
echo $row['lyrics'];
echo $row['alphabet'];
}
?>

I'm newbie. Please help. Thank you.

Shailesh Katarmal · Accepted Answer · 2015-09-25 10:02:50Z

1

$alphabet = $_GET['alphabet'];
$id = mysqli_real_escape_string($conn,$id);
$query = "SELECT * FROM `lyrics` WHERE `alphabet`='" . $alphabet . "'";

replace with:

$alphabet = mysqli_real_escape_string($conn,$_GET['browse']);
$query = "SELECT * FROM `lyrics` WHERE `alphabet`= '" . $alphabet . "' ";

answered Sep 25, 2015 at 10:02

Shailesh Katarmal

2,7931 gold badge15 silver badges16 bronze badges

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

Comments

NDM · Accepted Answer · 2015-09-25 09:56:05Z

1

You propose the following URL: publicsearch.php?browse=a

But in your script you are using the request parameter $_GET['alphabet']

In addition, you don't need a separate column alphabet in your database, you can use SQL's LIKE:

$letter = $_GET['browse'];
$sql = "SELECT * FROM `lyrics` WHERE title LIKE '{$letter}%'"

answered Sep 25, 2015 at 9:56

NDM

6,8483 gold badges42 silver badges55 bronze badges

Collectives™ on Stack Overflow

Get data from MySQL database table by specific field in url

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related