Php, Sometime A Strange Thing Is Happen When Get Data From Db

[pascal_22]

Hello !

I don't know how explain my problem but i will try

 

On the user profile, i show birthday, sex, age, height..........

I get all data this way:

while($row = mysql_fetch_assoc($MonProfil))
{
$sex=$row["sexe"];
...
...
}

and i display like that

sexe:<?php echo $sex;?>

height:<?php echo $height;?>

Birthday:<?php echo $birthday;?>

 

 

And when consulting some user profil, some times the height show nothing.. like a empty string.... if i refresh the page, it appears....

 

 

 

Do you know what is happenning.

 

I am on a dedicated server in linux. And PHP and MYSQL if up to date...

 

thanks a lot!

[Jessica]

What's the query you use to select the data? Are there duplicate rows for the users in any way?

[pascal_22]

Hello Jessica!

 

Thanks for your reply !!

Here are my query...

 

 

$strSql = "select * from Profils inner join ........... where Profils.NomUsager='" . $NomUsager . "'"; 
$MonProfil = mysql_query($strSql,$cn) or die(mysql_error()); 

 

It's that way....

 

Is that correct!!

 

Thanks!

 

Pascal

[Psycho]

From what you provided it appears you are only getting the data for one record. Why, then, are you using a while() loop to extract the record?

 

That aside, there is nothing from the code you have provided that would explain the behavior you described.

 

One possibility is that the data is changing between page loads (e.g. someone added a height value), but I assume you are wortking from "known" data.

 

Other than that, there has to be some other aspect to the functionality that you have not shared that is causing this. Are you using any data that is stored in session or cookie data? Are you sure that you are getting the data for the record you expect and that the data between the two page loads isn't really from two different records?

 

One other possibility is that it is a cachng issue in the browser.

[pascal_22]

Hello Psycho,

 

You right... i dont know why i put it in a while() loop... there only one profil per member....

 

All data, sexe,height,income,ethnie...... are all combo box... they are all known data.... when the display are no correctt(missing infos), i do refresh and everything's good... sometimes it needs 5-6 refresh before the display is good....

 

No coockie or sessions... i pass the member name is url (url rewrite) and i fill my variable as shown on my first post...

 

I'll try to make it happen and ill check if it's same data that show .. to see if it's same record!

 

How work image here... can i post two image... one when working and other when not working...?

 

Thanks

Pascal

[pascal_22]

Yes it's the same record! Good info..

 

it just don't show all info....... strange hein?

 

Thanks for your help!

Pascal

[pascal_22]

Anyone have an idea?

Thanks

[Jessica]

Echo the query to the screen, and then run it directly in MySQL/phpMyAdmin. What do you get?

[Psycho]

In addition to the above suggestion, I would also have the record ID added to the output for debugging purposes.

 

But, if the query IS the same one being run each time then you should be getting back the same record. Well, unless you have some code that is modifying the "NomUsager" value for your records. Or, there could be some logic in the output code that is suppressing the display of certain fields. You have provided no code aside from the dynamic query - so all of this is just hypothetical guesses.

 

You need to implement some debugging techniqes to see what is really happening. In addition to echo'ing the query I would do something such as this:

 

$strSql = "select * from Profils inner join ........... where Profils.NomUsager='" . $NomUsager . "'";
$MonProfil = mysql_query($strSql,$cn) or die(mysql_error());
$profile = mysql_fetch_assoc($MonProfil);
echo "Query: {$strSql}<br>\n";
echo "Record Count: " . mysql_num_rows($MonProfil) . "<br>\n";
echo "Date:<pre>\n" . print_r($profile, 1) . "</pre><br>\n";

 

Run your code and pay close attention to the data that is output. See what changes when the problem occurs.

[pascal_22]

Yes ok ià,ll try it now!

 

so... put data in variables like:

$name=$profile["name"]

$age=$profile["age"]

$height=$profile["height"]

is not good?

 

I was doing that because i was in a while loop, but i dont need a loop so i can delete all those variable and only use $profile?

thanks

[pascal_22]

ok now...

 

I've deleted the while loop, now i use:$profile = mysql_fetch_assoc($MonProfil);

 

I try to make it happen(the bug) and for now all is good!!

I'm pretty sure isn't the loop who make that bug...... No?

 

And to answer to a question, ask previously by Jessica and Psycho, it's not a dynamic query, it's only the same, only de NomUsager change...

 

also, for better performance: should i continue to put data in variable like:

$age=$profile["age"];

$height=$profile["height"];

$datebirth=$profile["datebirth"];

and so on ... and after echo it

 

OR

only use $profile["age"] when i display it like:

echo "age:".$profile[age];

 

 

 

Thanks a lot

[Jessica]

 

And to answer to a question, ask previously by Jessica and Psycho, it's not a dynamic query, it's only the same, only de NomUsager change...

 

 

So you just ignore our attempts to help...ok.

 

 

[pascal_22]

no Jessica! i don't ignore your help? What you mean?

 

Sorry i dont understand.... i try what you said!!

 

Sorry

 

Thanks

[Jessica]

I think you have some messed up data. To try to eliminate that as a possibility, I suggest you run the actual query in mysql/phpmyadmin, and to get the accurate query you'll want to echo it after building it.

 

$strSql = "select * from Profils inner join ........... where Profils.NomUsager='" . $NomUsager . "'"; 
echo $strSql; // Add this
$MonProfil = mysql_query($strSql,$cn) or die(mysql_error()); 

 

And run it in your choice of mysql interface and tell me if you get 1 row or more.

[Psycho]

And to answer to a question, ask previously by Jessica and Psycho, it's not a dynamic query, it's only the same, only de NomUsager change...

Um, if ANY parameters in the query change - then the query IS dynamic.

 

also, for better performance: should i continue to put data in variable like:

$age=$profile["age"];

$height=$profile["height"];

$datebirth=$profile["datebirth"];

and so on ... and after echo it

 

OR

only use $profile["age"] when i display it like:

echo "age:".$profile[age];

Why would you think that performance would be improved by creating new variables for data you already have stored in another variable. Using the array $profile would be better in terms of performance, but any performance benefit would be miniscule. I just see no reason to create new variables as it only creates code bloat.

 

I still think the problem has something to do with the code you are using to output the results and NOT the query. But, since you have not shared that info I'm not sure what else we can do to help.

[pascal_22]

Jessica, i ran it in PHPMYADMIN and i get one row !!! So that is normal!

 

oups sorry Psycho... yeah a dynamic query!

 

ok, so i'll change my code to only use $profile array

 

I will try to share everything that can help......

 

in a member profil, we can see detailled description, like: eye color, hair color,incomes(exemple:0-15000$, 15000$-30000$)..... and in my database, in each fields, i store the value (id)

exemple: brown hair has id 1, so in fields colorhair from the table profiles, i store 2 (the id)... like a foreingkey...

 

Last year, i had a table for, eyecolor, haircolor, income.........., an other...... and when i displayed the page profile, i did a another query to get the description of the value, so one query to get description for haircolor, another for to get description for eye color.... and so one....

 

And last year, someone told my to stop to do a lot of query to my DB, only put static data in array..

So that what i done.

 

so for 'static data' that do not change frequently, i store it in array

exemple:

//french: yeux=eye
$tabYeux[1] = "Bleu";
$tabYeux[2] = "Vert";
$tabYeux[3] = "Pair";
$tabYeux[4] = "Brun";
$tabYeux[5] = "Noir";


//cheveux = hair
$tabCheveux[1] = "Brun";
$tabCheveux[2] = "Noir";
$tabCheveux[3] = "Blond";
$tabCheveux[4] = "Roux";
$tabCheveux[5] = "Châtain";
$tabCheveux[6] = "Plusieurs couleurs";
$tabCheveux[7] = "Autre couleur";

so if we come back to my page profile, to get hair color i do:

$Cheveux = $tabCheveux[$profile["FkCheveux"]];

so further in my code, when i want echo the hair color, i do :

<p><?php echo $Cheveux;?></p>

 

Hope this can help.....

 

Thanks

Pascal

Edited September 26, 2012 by pascal_22

[Psycho]

So, you are getting values from the DB and then using some logic to determine the correct text to display, correct? And, we have gone through 15 posts in this thread before you felt the need to share that information? :-\

 

If you ran the debugging code I provided above and the results were consistently as expected then you should look into the logic to determine the correct text based on language. Don't know why we didn't suspect it was something like this before

I still think the problem has something to do with the code you are using to output the results and NOT the query.

 

Oh wait, I did.

Edited September 26, 2012 by Psycho

[Christian F.]

Whomever told you to stop storing "static" data in the database, information that you clearly have to store in the database anyway, was being an idiot when he said that. Sorry to say so, but there is no other way of stating it.

 

What you should have done, is to make those fields ENUMs and list all of the available options in the table-definition. Keep all data that's related to the data in the database, actually in the database.

Besides, the data isn't "static" either way. It's clearly being provided on a per-uses basis, and changes for each individual user. It's just from a limited selection, which is not the same as "static".

 

Secondly, I really think you should read this article, and adhere to its advice. You have, time and time again, refrained from posting actual useful information that we needed to help you, despite people asking (repeatedly). You're making way too many assumptions about what the problem is, when you have no idea (otherwise you wouldn't be asking us), which leads to bad decisions like cutting away the central piece of the SQL query, not mentioning half of the steps involved in producing the error, and so forth.

Edited September 26, 2012 by Christian F.

[DavidAM]

...

Last year, i had a table for, eyecolor, haircolor, income.........., an other...... and when i displayed the page profile, i did a another query to get the description of the value, so one query to get description for haircolor, another for to get description for eye color.... and so one....

...

And last year, someone told my to stop to do a lot of query to my DB

 

 

You don't need to do "another query" to get the description of an item. You use a JOIN and get it all in the original query. If the items (i.e. eye color) are optional, you can use a LEFT JOIN. If the tables are properly structured and indexed, the performance will be better than multiple queries.

[pascal_22]

Ok...!!

i did wrong.. sorry if i didn't share that information, at the beginning, i didn't think that it was a very importance....

So Christian, i'll read the article you share.

 

In fact, i should use INNER JOIN or LEFT JION instead of array. That's what i will do.

 

If i come back, to last year, when i did that change(DB to array) it's because, i use it in profile page and also i use it when user create their profile to fill all combobox... so i thought it was to much query

i mean: query to fill combobox eye color, query to fill combobox for hair color..... and so much more.... so i thought that using array instead of DB was a good choice...? isn't?

 

i have a total of 14 combobox that are filling by DB? is'nt too much...?

 

Thanks!

Pascal

[Christian F.]

It's all good, as long as you take our advice to heart.

 

And 14 fields isn't too much for the database, and if your users accepts it then it's all good. Having the data stored in an array, as well as ENUM fields in the database, might help speed up the application a bit. Though, I doubt it'll be any meaningful gain. On the other hand, it creates a situation where you can have data that's out of sync, and thus cause issues in the future.

That's why I recommend using a single query to retrieve the information needed from the database, to build those arrays.

[Psycho]

If i come back, to last year, when i did that change(DB to array) it's because, i use it in profile page and also i use it when user create their profile to fill all combobox... so i thought it was to much query

i mean: query to fill combobox eye color, query to fill combobox for hair color..... and so much more.... so i thought that using array instead of DB was a good choice...? isn't?

 

No, it's not a good choice. There are many reasons but the one that I think is the most important is that by separating the "values" from the "data" makes the application much more difficult to maintain. You apparently have a list of eye colors, for example. In the database you are storing a 1, 2, etc. for the eye color. Then when you want to display the data you have to translate that ID into the value against the array. You also need to use the array when creating the form fields. Now, let's say you need to modify the list in the future. You have to make sure to keep the array absolutely in sync with the data in the database. Also, it makes it impossible to understand the data int he database without the arrays.

 

Since it appears you are dealing with different languages here would be my recommendation:

 

Create a separate table for each attribute description (e.g. "attr_eye_colors", "attr_hair_color", etc.). In these tables you would have a column for the id and then multiple columns for each language you support. I would name these columns using the standardized language code: either "fr" for french or if you want to be more specific "fr-ca" for french Canadian. Then when you need to get a user's data your query might look something like this:

$query = "SELECt Profils.id, Profils.name,
			 att_eye_color.$lang as eye_color,
			 att_hair_color.$lang as hair_color
	  FROM Profils AS u
	  LEFT JOIN attr_eye_color
	    ON Profils.eye_color_id = attr_eye_color.eye_color_id
	  LEFT JOIN attr_hair_color
	    ON Profils.hair_color_id = attr_hair_color.hair_color_id
	  WHERE Profils.NomUsager='$NomUsager'";

$lang would be a variable that you set for the user (I'd probably store as a session variable) with the value of the appropriate language code for the user.

[pascal_22]

Thanks for all great answer!!

I'm making the change now!! I'm pretty sure it will solve that case!!

I'll let you know if that works!!!

Thanks a lot for the advicesss!!

Thanks so much!

Pascal

[pascal_22]

Just to tell you that everything is working!!!

I delete the array and i go directly in database

Now all is good!!

 

thanks!!

 

Pascal