Saved/Stored Query Mysql

[LeonardK]

Hi there,

I want to execute a query that i saved in my database like this:

ID | NAME | QUERY
1 | show_names | "SELECT names.first, names.last FROM names;"
2 | show_5_cities | "SELECT cities.city FROM city WHERE id = 4;"

Is this possible ?
I am kinda noob in php so plz explain if it is possible.

[arunmagar]

If you are going to use value from column 'QUERY', then there will not be any problem.
Just make sure that column name and table name are proper.
In php write a code to select which query you want to execute and pass it to the mysql_query function.

[pritaeas]

Why not create views instead?

[LeonardK]

sorry maybe i should be more clear:

What I want is:
1. Execute the query from database.
2. Put the results from that query in an array.
3. Put the array into a CSV File

I want to execute a query that i saved in my database like this:

DB name = world
Table name = queries

ID | NAME               | QUERY
 1 | show_city_names    | "SELECT country.city_names FROM country;"
 2 | show_all           | "SELECT * FROM country;"

If the query is executed, I want to put it in an array.
After the query is put in an array I want it to save it into a CSV file that can be downloaded on my website.
I hope you guys can help. I am kinda noob in php so plz explain if it is possible.

[arunmagar]

@pritaeas I am not much familiar with VIEWS

But this way also we can do. I have given you way to your solution. Not whole logic.

Suppose store your query in $query="SELECT country.city_names FROM country;" For this you to have to query on the table or the value how ever you get. And also make sure that double quotes are not there in the query because you shown double quotes around the query.

Travese the result which you get from $query

I am using mysql_query function that is why I have written.
Instead of mysql_query and mysql_fetch_array function, please use respective function from Php's new mysqli extension. Because mysql_query and mysql_fetch_array are deprecated.

//First create connection with mysql

$countryCityNameArr=array();      //Array to store values
$sqlCountry="SELECT country.city_names FROM country;";
$queryCountry=mysql_query($sqlCountry,$conn);
while($resultCountry=mysql_fetch_array($queryCountry))
{
    $countryCityNameArr[]=$resultCountry['city_names'];
}

//create handle for csv file in which you want to write the data
$fp=fopen("CityName.csv","w");
foreach($countryCityNameArr as $city)
{
    fwrite($fp,"$city\n");  
}

fclose($fp);

I think it depends on your usage. Views are certainly easier, e.g.

"CREATE VIEW v AS SELECT field1, field2, FROM table1"

and then when you need it...

"SELECT * FROM v"

However, if you're going to create loads of queries or change them constantly, perhaps a table could work for you. The only difficulty with the View approach is easy retieval of metadata, but I suppose you can just do this:

"SHOW CREATE VIEW"

and that will give you a list of views and metadata

[LeonardK]

@arunmagar ty for the ellp got that working. but i still have 1 question.
u use:

$sqlCountry="SELECT country.city_names FROM country;";

But can u explain to me how i change this into a function.
So that i can use a query that i already saved in the database, to get the CSV file.

Hope i made myself clear, ty for the help.

[pritaeas]

Something like this perhaps:

function GetQueryResults($queryName) {
    $results = array ();
    $result = mysql_query("SELECT * FROM `queries` WHERE `name` = '$queryName'");
    if ($result) {
        $row = mysql_fetch_assoc($result);
        if ($row) {
            $queryResult = mysql_query($row['name']);
            if ($queryResult) {
                while ($record = mysql_fetch_assoc($queryResult)) {
                    $results[] = $record;
                }
            }
        }
    }
    return $results;
}

You can fill in the missing pieces.

[LeonardK]

@arunmagar ty for the help this code was very helpfull, got it working with a normal query.
the thing i want to do now implement @pritaeas code with your code so it works.

@pritaeas

im not a very good programmer but i need to implement this function for my project.
But i dont know how to get this to work, show it on my site and connect it with my database.

Could you please help me ?

King Regards

Leonard

[pritaeas]

But i dont know how to get this to work, show it on my site and connect it with my database.

Can you be a little more specific. What do you need to get working, that is not working now?

[LeonardK]

i got you code like this:

    function GetQueryResults($queryId) {
    $results = array ();
    $result = mysql_query("SELECT * FROM rapport_query WHERE id = '$queryId'");
    if ($result) {
        $row = mysql_fetch_assoc($result);
        if ($row) {
            $queryResult = mysql_query($row['id']);
            if ($queryResult) {
                while ($record = mysql_fetch_assoc($queryResult)) {
                    $results[] = $record;
                }
            }
        }
    }

    return $results;

}

and what i need to do now is connect it with my database so i can show the result on my website.
because if i load it like this it gives me a blank page.

[pritaeas]

You'll need to add the mysql_connect and mysql_select_db. If you're not sure, there's a code snippet in the PHP forum code snippets tab showing how.

[LeonardK]

Something like this ? :

<?php

class export_csv
{

    function GetQueryResults($queryId) {

        mysql_connect('localhost', 'root','')
            or die ("Could not connect to server ... \n" . mysql_error ());
        mysql_select_db('rapport')
            or die ("Could not connect to database ... \n" . mysql_error ());

        $results = array ();
        $result = mysql_query("SELECT * FROM rapport_query WHERE id = '$queryId'");
        if ($result) {
            $row = mysql_fetch_assoc($result);
            if ($row) {
                $queryResult = mysql_query($row['id']);
                if ($queryResult) {
                    while ($record = mysql_fetch_assoc($queryResult)) {
                        $results[] = $record;
                    }
                }
            }
        }
        return $results;

    }
}
?>

[pritaeas]

Right. What I would do is add the database credentials as parameters to the class constructor. That way you don't have to change your class if you want to use this somewhere else. Something like this:

class export_csv
{
    private $host;
    private $user;
    private $password;
    private $database;

    function __construct($host, $user, $password, $database)
    {
        $this->host = $host;
        // etc.
    }

    public function GetQueryResults($queryId)
    {
        mysql_connect($this->host, $this->user, $this->password);
        // etc.
    }
}

[LeonardK]

I will update this code after it works:

<?php
    class export_csv
    {
    public function GetQueryResults($queryId) {
    mysql_connect('localhost', 'root','')
    or die ("Could not connect to server ... \n" . mysql_error ());
    mysql_select_db('rapport')
    or die ("Could not connect to database ... \n" . mysql_error ());
    $results = array ();
    $result = mysql_query("SELECT * FROM rapport_query WHERE id = '$queryId'");
    if ($result) {
    $row = mysql_fetch_assoc($result);
    if ($row) {
    $queryResult = mysql_query($row['id']);
    if ($queryResult) {
    while ($record = mysql_fetch_assoc($queryResult)) {
    $results[] = $record;
    }
    }
    }
    }
    return $results;
    }
    }
    ?>

How do i get this on screen so i get some results on my screen ?
i will update the code after it works so i can test if i can get it to work myself

[pritaeas]

$export = new export_csv();
$results = $export->GetQueryResults('YourId');
print_r($results);