Member Avatar for dourvas

Happy new year to everyone..

in my sql db there r 2 tables. mathimata and apousies

from table -apousies- i "select" some data (the amount of absences) for a particular person (student) for every class he attended.
i want for every class of the above result to search in table -mathimata- and to find the board_of_absences of that classes (the field orio_apousiwn)


code
...

$qry="SELECT semester, math, apousies FROM apousies WHERE member='$all' order by semester"; // i get semester, class and absences for a particular student

$result=mysql_query($qry);

if($result) {
while($grammh = mysql_fetch_array($result)) {

$result2=mysql_query(" Select distinct orio_apousiwn from mathimata where mathima='$grammh[math]' and semester='$grammh[semester]' ");

$orio =mysql_fetch_assoc($result2);
$or1=$orio[orio_apousiwn];

echo "Semester: $grammh[semester], class: $grammh[math], absences: $grammh[apousies], board_of_absences: $or1"."<BR>";}

exit(); }

The script is working. the data shows results except the first line of results that only board_of_absences ( orio_apousiwn) is empty

one result:

Semester:A, class: Γλώσσα, absences: 23, board_of_absences:
Semester:A, class: Φυσική, absences: 12, board_of_absences: 22
Semester:A, class: Χρήση Η/Υ, absences: 4, board_of_absences: 16

the 2 and 3 line are ok in 1 there is no value in board_of_absences: but it should be........

i can not understand why.....

can u help me?????

  • 2 Contributors
  • 4 Replies
  • 181 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by DiGSGRL

Dani AI

Generated

Most likely cause: the semester value in the two tables is not the same byte-for-byte (for example a visually identical character from a different alphabet, hidden whitespace, or different encoding). That explains why two classes join correctly but one returns an empty orio_apousiwn. was correct that DISTINCT is irrelevant here and using quoted associative keys (e.g. $row['semester']) is safer in PHP — but that change alone won't fix a data mismatch in the DB.

Fast checks to run (replace the placeholders with the actual values):

-- inspect raw bytes for a particular class/row
SELECT semester, HEX(semester) FROM mathimata WHERE mathima = '<class>';

SELECT semester, HEX(semester) FROM apousies WHERE math = '<class>' AND member = '<member>';

-- show both sides and the HEX values to spot differences
SELECT a.semester AS ap_sem, HEX(a.semester) AS ap_hex,
       m.semester AS ma_sem, HEX(m.semester) AS ma_hex,
       m.orio_apousiwn
FROM apousies a
LEFT JOIN mathimata m ON m.mathima = a.math
WHERE a.member = '<member>';

If the HEX values differ, normalize the data (take a backup first) or update rows that use the wrong codepoints. Example technique using the HEX you find:

-- after verifying the HEX matches the bad byte sequence
UPDATE mathimata
SET semester = 'A'
WHERE HEX(semester) = '<bad_hex>';

For correctness and performance, pull the limit value with one query instead of per-row queries:

SELECT a.semester, a.math, a.apousies, m.orio_apousiwn
FROM apousies a
LEFT JOIN mathimata m
  ON m.mathima = a.math AND m.semester = a.semester
WHERE a.member = '<member>'
ORDER BY a.semester;

Other tips: TRIM() any stray spaces, check table/column character sets and collations, and move to parameterized queries (mysqli or PDO) to avoid injection and parsing surprises. Back up data before mass updates.

Recommended Answers

All 4 Replies

Member Avatar for dourvas

anyone.....

Member Avatar for DiGSGRL

What is the number you are expecting to be in the first slot?
Also, have you checked to make sure all the numbers are correct?
And why are you using DISTINCT here:

Select distinct orio_apousiwn

Are all of those values expected to be unique?

Member Avatar for dourvas

yes, all the values are unique. U re right the distinct is not nessecery but for sure it doesnt cause the problem. i tried with or without it.

i will give u the data from table mathimata and from table apousies. it might help..

table mathimata

"1","1","Χρήση Η/Υ","A","16"
"2","1","Φυσική","A","22"
"3","2","Οικοκυρικά","Α","30"
"5","1","Γλώσσα","Α","16"
"8","2","Γεωγραφία","A","25"
"6","4","Ιστορία","A","23"
"7","4","Ιστορία τέχνης","A","12"
"9","2","Γυμναστική","A","12"


table apousies

"1","ΠΑΠΑΔΟΠΟΥΛΟΣ, ΓΕΩΡΓΙΟΣ , ΓΡΗΓΟΡΙΟΣ","1","A","Γλώσσα","23"
"2","ΠΑΠΑΔΟΠΟΥΛΟΣ, ΓΕΩΡΓΙΟΣ , ΓΡΗΓΟΡΙΟΣ","1","A","Φυσική","12"
"3","ΠΑΠΑΔΟΠΟΥΛΟΣ, ΓΕΩΡΓΙΟΣ , ΓΡΗΓΟΡΙΟΣ","1","A","Χρήση Η/Υ","4"
"5","Χαραλαμπίδου, Μαρία, Βασίλειος","2","A","Οικοκυρικά","12"
"6","Χαραλαμπίδου, Μαρία, Βασίλειος","2","A","Γυμναστική","11"
"7","Χαραλαμπίδου, Μαρία, Βασίλειος","2","A","Γεωγραφία","10"

in the result i and published in my previous post (for student ΠΑΠΑΔΟΠΟΥΛΟΣ, ΓΕΩΡΓΙΟΣ) iwas expecting the field orio_apousiwn (board_of_absences) to has value 23.

the data is in greek and i am not sure if i help or comfuse more by posting them

Member Avatar for DiGSGRL

If this is your exact code you may be having inconsistencies due to syntax.

Change:
echo "Semester: $grammh[semester], class: $grammh[math], absences: $grammh[apousies], board_of_absences: $or1"."<BR>";
To:
echo "Semester: ". $grammh['semester'].", class: ".$grammh['math'].", absences: ".$grammh['apousies'].", board_of_absences: ".$or1."<BR>";

If that does not help I would next take the second queries results stick it in its own loop and echo all of its results with the number key to make sure that comes out correct.

the data is in greek and i am not sure if i help or comfuse more by posting them

Its all Greek to me.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.