[SOLVED] Help returning data from MySQL database...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Mark Bowen
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 4:38 am
Location: United Kingdom | Birmingham

[SOLVED] Help returning data from MySQL database...

Post by Mark Bowen »

Hi there,

Just wondering if anyone can help me with this!

What I would like to do is return information from a MySQL database and display it in Flash. I can do all of this fine but my problem arises from the fact that I am going to be creating around 200 search engines for a site and do not want to have to code out all the php scripts for every single field that a query returns information for.

What I would ideally love to be able to do is send a query to a database in the usual way, bring it back into an array as it does now and then spit out all the results in the standard Flash variables format.
However I do not want to have to put this kind of code ;

Code: Select all

echo "&colour" .$count ."=" . $returnedColour;
all over my PHP files as there may be 100 or more terms that will be returned for each search engine and when there are approximately 200 or so engines a couple of thousand lines of PHP code doesn't sound a very appealing prospect!! :-)

So basically what I am looking at finding is a way of taking the result set (Array) and spitting out the name of the fields and their associated values for each row of data.

If anyone has an idea of what I am trying to achieve and can supply me with some code to help on this then I would be exceptionally greatful.

Regards,

Mark Bowen
Mark Bowen
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 4:38 am
Location: United Kingdom | Birmingham

Post by Mark Bowen »

Bump. Sorry! I forgot to put on Notify of replies!!

Regards,

Mark Bowen
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

i do

Post by phpScott »

I do the following

Code: Select all

<?php
while($row = mysql_fetch_assoc($resultSet))
{
   foreach ($row as $key => $value)
   {
	$resultsArr[$x][$key]=$value;
   }
   $x++;
}
?>
Which takes the column name from the table and creates and named arrayed as in $resultArr[0]['columnName']
Which for me anyway makes using the result set easier in my code because I know the columns that I am using.
Mark Bowen
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 4:38 am
Location: United Kingdom | Birmingham

Couldn't get it to work....

Post by Mark Bowen »

Hi phpScott,

Thankyou for your reply but I couldn't get the supplied code to work. It came up with an error saying that mysql_fetch_assoc is not a valid MySQL resource!!

Perhaps you could be so kind to write up the complete code for me as I have been trying for ages to get this to work but I am not all that good at PHP as such.

What I would really like is this :

Let's say the database has three fields - colour, type and length. Let's say I now have some records with different values for those fields.
What I need to be able to do is to :

Pull out the details as normal but then without having to write :

Code: Select all

echo "colour" . $count . "=" . $colour;
echo "type" . $count . "=" . $type;
echo "length" . $count . "=" . $length;
for all the fields (there will be more than 3 in the real case scenario) have the information displayed in that way but without writing it out by some way of finding out the corresponding field name and then putting the data into it.
The code above is the way that I would need the output formatting so that I can use it in a Flash environment but if you can just supply me with code so that I can do a SELECT query on a table in a database and then from the returned array result be able to get to the different field names and their associated values for each column and row then that would be exceptionally helpful.

Thanks for all your help so far and I really hope you can help me out on this as it has me completely stumped!!

Regards,

Mark Bowen

[/list]
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If you do a SELECT as normal, then once you have run the query ([php_man]mysql_query[/php_man]()) you could do the following (I'm guessing that $count is the row number from the result set?):

Code: Select all

$sql = "your SQL query";
$result = mysql_query($sql) or die(mysql_error());

$count = 1;
while ($row = mysql_fetch_assoc($result)) {

    foreach ($row as $key => $value) {
        echo $key.$count.'='.$value;
    }
    
    // increment $count by 1
    $count++;
}
Mac
Mark Bowen
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 4:38 am
Location: United Kingdom | Birmingham

Absolutely fantastic!!!!

Post by Mark Bowen »

Hi Mac,

Just wanted to say a massive massive thankyou!!!! :-)

The code worked absolutely perfectly!!

All I have to do now is find out exactly what it all means so that I can understand and keep on using it next time!

If you could possibly (if you have the time) let me know (not totally in laymans terms as I can understand a little of PHP when explained to me! :-) ) what it all means then I would be in eternal debt to you.

Thanks once again for all of your help regarding this!!

Mark
Mark Bowen
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 4:38 am
Location: United Kingdom | Birmingham

Post by Mark Bowen »

Sorry about the fact that the forum has moved my post all the way to the right!! Not too sure why it has done that. Have seen that before on Forums and always wondered what that is??

8O

Mark
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

twig

Post by phpScott »

twig's got it.
The $result is the

Code: Select all

<?php
$result=mysql_query($yourQuery)  or die(mysql_error());

$count = 0;
while ($row = mysql_fetch_assoc($result)) {

    foreach ($row as $key => $value) 
   {
      $resultsArr[$x][$key]=$value;
    }
    
    // increment $count by 1
    $count++;
}
?>
The $count is just used to increment the array.
I know I could do it without it but it seemed better to me anyway to be exact in what I am counting.
Not only that but you could use $count-1 to get the number of rows returned.

If you need more help send me a message and I will send you the rest of the code that I use. This is just a little part of the DB class that I created and continue to modify.

phpScott
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Glad it helped :), if you give a little more detail on which bits you aren't sure on I'd be happy to give a bit more of an explanation.

Mac
Mark Bowen
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 4:38 am
Location: United Kingdom | Birmingham

Post by Mark Bowen »

Hi Mac,

Just one last query.

I was just wondering how easy it would be to be able to miss out certain fields from the returned results?

The reason I ask is this. In the table there will be the three fields as I described before but also an id field as most databases have and possibly other fields that I also don't want ouputted.
How easy would it be to reate an array to hold the fields I don't want returned and when the while loop executes check if the field is one of those in the array and disclude it.

Any help you can give on this would be excellent.

Thanks again for all your help so far.

Regards,

Mark Bowen
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Would you need the excluded field results at all? If not you could create a SELECT statement that only returns the fields you want, e.g.

Code: Select all

SELECT colour, type, length FROM table WHERE blah blah blah
If you will need the excluded field results for another bit, then you could do:

Code: Select all

// create an array with field names we don't want to output
$excluded_fields = array('id', 'exclude1', 'exclude2');

$count = 1;
while ($row = mysql_fetch_assoc($result)) {

    foreach ($row as $key => $value) {
        // check that the $key (the field name) isn't listed in the $excluded_fields
        // array so we only output fields we want
        if (!in_array($key, $excluded_fields)) {
            echo $key.$count.'='.$value;
        }
    }
    
    // increment $count by 1
    $count++;
}
Mac
Last edited by twigletmac on Thu Sep 30, 2004 9:22 am, edited 1 time in total.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

your query

Post by phpScott »

That can come down to the your query. Don't put them in the Select part of the query.
Altenatively

Code: Select all

<?php
$dontInclude=('id', 'count', 'someOtherField');//array of items not to include
$result = mysql_query($sql) or die(mysql_error());

$count = 1;
while ($row = mysql_fetch_assoc($result)) {

    foreach ($row as $key => $value) {
     if(!in_array($key, $dontInclude))
        echo $key.$count.'='.$value;
    }
    
    // increment $count by 1
    $count++;
}

?>
notice the if() statment to check the[php_man] in_array[/php_man]

EDIT:damn Twig beat me to it agian.

phpScott :cry:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Re: your query

Post by twigletmac »

phpScott wrote:EDIT:damn Twig beat me to it agian.

phpScott :cry:
At least it shows the same approach twice :) kinda like a second opinion...

Mac
Mark Bowen
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 4:38 am
Location: United Kingdom | Birmingham

My hat's off to you both...

Post by Mark Bowen »

Hi there,

Thankyou ever so much for the code snippets. You have both been exceptionally helpful and I will try this code out immediately although I am 1,000,000% certain that it will work coming from you guys.

Many many thanks,

Mark
Mark Bowen
Forum Newbie
Posts: 13
Joined: Thu Sep 30, 2004 4:38 am
Location: United Kingdom | Birmingham

Post by Mark Bowen »

Hi there,

Me again!!

Just another (I'm sure for you guys as you are fantastic!) quicky.

I now have everything working brilliantly. I am able to query the database and return the data that I need and display it in Flash.
What I now need to be able to do is in Flash I have checkBoxes that use the fieldNames from the code you supplied so that I can click on them and produce a true / false answer. I can then send all of this to a php file so that this is the kind of URL it will send :

Code: Select all

http://www.mySite.com/searchData.php?colour=red&type=wooden&length=long
Now as I said before the search will probably end up having a very large amount of checkboxes so I would like to get away from having to write :

Code: Select all

"SELECT * FROM myDatabase WHERE colour LIKE '$colour%' AND type LIKE '$type%' AND length LIKE '$length%'"
as my SELECT query as the more criteria there are then the more and more of that kind of code I will have to write.

What I am asking therefore is, is there a way of using a modified sample of the code that you supplied just return one of the field names from the database rows as at the moment I have my database setup so that there is an id field a searchTerm field and a name field. ID is the usual increment value, searchTerm is the colour, type and length and name is 'Please select a colour', 'Please select a type', 'Please select a length'. The names field is just used so that Flash can name the checkboxes how I want them.

What I would need in this case is to just return the searchTerm field from the same data as before and then use this to create a SELECT query and use the data that is being supplied by the URL shown before.

I hope I explained this well enough and any help you can give me on this would be absolutely awesome. I have been trying for ages to modify the code you supplied but I just can't get the one field only to come up in the printed results.

Regards,

Mark Bowen
Locked