/    Sign up×
Community /Pin to ProfileBookmark

Inner Join in PHP

__(Added `[code]…[/code]` tags ~ MOD)__

I have two tables in db Tenses and tensecategories … Now i want to use inner join and display values on the page ?

[code]<?php
$getcat = mysqli_query($conn, “SELECT * FROM tensecategory t, tenses c WHERE t.tenid=c.pktenseid”);
$i =1;
while($cat = mysqli_fetch_assoc($getcat))
{
$id = $cat[‘pkcategoryid’];
$cat_name = $cat[‘cat_name’];
$tenid = $cat[‘tense_name’];
//$tensess = $cat[‘tense_name’];
?>

<tr>
<td><?php echo $i; ?></td>
<td><?php echo $cat_name; ?></td>
<td><?php echo $tenid; ?></td>
[/code]

to post a comment
PHP

60 Comments(s)

Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — [upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-07/1578383609-112382-tensess.png]

[upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-07/1578383619-858998-tencat.png]

tenid values are the id of tenses from ist table ---now i want to display by using inner join select query? but it shows only one value
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — Are you trying to break out the (improperly) stored values in the "tenid" column you displayed in your table image? If so, you will find that it is not easy. One should not have a design like this.
Copy linkTweet thisAlerts:
@NogDogJan 07.2020 — What is the data type for the teneid column? If it's some sort of character/text type, that's never going to work, since "1" does not equal "1,2,3". If it's an array type, then you'll need to use some sort of array function to compare values. Better would we be to change the DB design, possibly creating a separate relationship table between the other 2 tables, to support a many-to-many relationship, if that's what you're after.
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — NogDog is basically saying the same thing. You built your table/database wrongly for a true RDBMS. You cannot build a query that matches the multiple-valued 'tenid' column to anything that is not EXACTLY like it. Of course you could use a LIKE clause but that would only match against the first value or the a partial string value, etc. and that doesn't solve your problem, if we are reading your correctly.
Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — i fetch multiple record from tense table then i think inner join will not work ????

tenid data type is Varchar
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — Think about it. How do you compare a distinct value from one field to a set of values in another field???

Perhaps you should do a little reading up on proper db design. That may help you see why this is so difficult to do.
Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — tenid id values 1,2 3 is the multiple ids from tenses table ,,,
Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — @ginerjm#1612602 what you think this is the wrong way to create these 2 tables ??????
Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — @jasmine381#1612604 I just want to get id From First table into 2nd table ?? is this wrong way ??? Need your suggestion ?
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — What is the layout of this other table that we haven't seen yet?
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — It is not so much of a 'join' problem as it is a sql query problem. When you use a where clause to locate something in a table the compare that you write needs to have two distinct values to examine. If one of those values contains more than one value such as your strings of "1,2,3" and "14,15,16,17" how do you think a query engine will find the one single value that you want. Comparing a search value of "15" perhaps against your tenid value of "14,15,16,17" will fail. This is true for a where clause as well as an 'on' clause' in other types of joins.

You need to redefine your storage scheme for these "tenid" values. A table that contains each one in its own record that has its rows linked to those records in another table where those specific values all connect to.

My simple example of something would be like this:

John Doe is related to tenids of 1 & 3 & 5. You would create a set of 3 rows in this new table that have the following:

John Doe, 1

John Doe, 3

John Doe, 5

Now when you want to do a query that finds all of the tenids for a record with a key of 'John Doe' you simply do a query that joins the parent table to the tenids table

$q = "select a.name, b.tenid from primary_table a, tenid_table b

where a.name = b.name

order by a.name, b.tenid"
Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — @ginerjm#1612606 i uploaded both tables screenshots
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — And how do any of your 'pktenseid' values match exactly any of your 'tenid' values? When is '1' ever equal to "1,2,3"?? Sure you could use a LIKE to test '1' against '1%' but what you do to compare a 2 to this?
Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — @ginerjm#1612610 1 is ID of Present Tense and 2 is Present Perfect from Tenses table OK and i saved Tense row id in to TenseCategory (2nd Table) in tenid column ID like 1,2,3 (Prest Tense , Present Perfect, Imperfect),, i just save row IDs in to 2nd table ??
Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — @jasmine381#1612611 On 2nd Table 1 ,2 3 is the multiple Id from table one
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — Don't understand what you posted. But looking at your query:

SELECT * FROM tensecategory t, tenses c WHERE t.tenid=c.pktenseid

You are trying to compare a value such as what I said earlier: how is a pktenseid of '3' ever equal to a tenid of "1,2,3" ??
Copy linkTweet thisAlerts:
@NogDogJan 07.2020 — > @jasmine381#1612603 tenid id values 1,2 3 is the multiple ids from tenses table

Probably what is happening (I do not have access to a MySQL database right now to test it) is that when you compare the _string_ "1,2,3" to any numeric value, it is casting that string to a numeric type, which in that example would convert "1,2,3" to the number 1. Therefore you only get a match on the first number in the string (up to the first comma).

You _might_ be able to use the MySQL [find_in_set()](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set) function, something like:
<i>
</i>... where find_in_set(c.pktenseid, t.tenid) &gt; 0 ...

However, that is not going to scale well, as it will not be able to use any indexes and will have to do a full table scan -- maybe not an issue if you do not expect the table to get large. That's part of the reason you do not want multiple values in one table column most of the time, and may instead want to use a separate relation table for each possibl join between those 2 tables.
Copy linkTweet thisAlerts:
@100forumpostingJan 07.2020 — Can anybody please tell me what in echo in [PHP](https://thesoftking.com/offer/)
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — You're in the wrong topic. NO hijacking!
Copy linkTweet thisAlerts:
@jasmine381authorJan 07.2020 — @ginerjm#1612608 it shows empty result
Copy linkTweet thisAlerts:
@ginerjmJan 07.2020 — What?
Copy linkTweet thisAlerts:
@SempervivumJan 07.2020 — I agree with ginerjm and NogDog inasmauch as your database design is not optimal. However if you do not like to change it or it's not possible to do so, I recomment to code it in PHP. Then it would be easy to split the comma separated tenses into an array and process it.
Copy linkTweet thisAlerts:
@jasmine381authorJan 08.2020 — [upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-08/1578462505-6443-tensess.png]

[upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-08/1578462511-833186-tencat.png]

This is very simple and basic points ,,, First of all i have 2 tables ,, Tenses and TenseCategory ...

In Tenses table i just insert 1 to 14 tenses name & then get their ID;s in 2nd table in this form 1,2,3 (this is ID;s of 1st table data )

Now i want to display name by joining or some other method (Prest Tense, Present Perfect , Imperfect),, i will upload screen shot how it display on the page ,,
Copy linkTweet thisAlerts:
@jasmine381authorJan 08.2020 — [upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-08/1578463083-567529-display.png]

This actually i want to display in core php i think its not as such so complex , please help me regarding this :
Copy linkTweet thisAlerts:
@SempervivumJan 08.2020 — I'm a mysql noob but I took this as a chance to learn :D

I changed the tables to this:

table tenses

[upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-08/1578474285-469062-tenses.png]

table tensecategory

[upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-08/1578475424-367172-tense-cats.png]

Now a join like this works:

SELECT t.name, c.catid, c.catname FROM tenses t, tensecategory c WHERE t.catid=c.catid

However joining the tenses of one category grows a bit complex:
$sql = "SELECT t.name, c.catid, c.catname FROM tenses t, tensecategory c WHERE t.catid=c.catid";

$result = $pdo-&gt;query($sql);
var_dump($result-&gt;rowCount());
$tenseinfo = [];
foreach ($result as $row) {
if (!isset($tenseinfo[$row['catid']])) {
$tenseinfo[$row['catid']] = ['catname' =&gt; $row['catname'], 'tenses' =&gt; [$row['name']]];
} else {
$tenseinfo[$row['catid']]['tenses'][] = $row['name'];
}
}
var_dump($tenseinfo);

May be the mysql/php experts are able to simplify this. I tried to create a 2d array by using "group by" in the query but no success.
Copy linkTweet thisAlerts:
@NogDogJan 08.2020 — Based on the sample data, it looks like you should be associating from the Tenses table to the TenseCategory table, not the other way around as you're doing now. Then you could have just a single value in the new tenses.tense_category_id column (or whatever you prefer to call it) for each row.
Copy linkTweet thisAlerts:
@SempervivumJan 08.2020 — you should be associating from the Tenses table to the TenseCategory table, not the other way around as you're doing now.[/quote]I did so in my attempt.
Copy linkTweet thisAlerts:
@NogDogJan 08.2020 — > @Sempervivum#1612658 I did so in my attempt.

Yeah, I was addressing the OP, to hopefully point them toward a more "normalized" data schema. :)
Copy linkTweet thisAlerts:
@jasmine381authorJan 08.2020 — @Sempervivum#1612640 On single ID we can compare mulutiple table but now we have multiple IDs so i think join will not work :)
Copy linkTweet thisAlerts:
@SempervivumJan 08.2020 — @jasmine381#1612663 When using my database structure there is no need for having multiple IDs in one field. Instead we have multiple IDs distrubuted over multiple rows in table tenses.
Copy linkTweet thisAlerts:
@jasmine381authorJan 08.2020 — @Sempervivum#1612664 Yes you are right but i need to get multiple ID;s from other table and display on the page as you saw i uploded the screen i have multiple ID 1,2 3 from other table ,,,,,
Copy linkTweet thisAlerts:
@SempervivumJan 08.2020 — No problem to display that data in a table:
$result = $pdo-&gt;query($sql);
$tenseinfo = [];
foreach ($result as $row) {
if (!isset($tenseinfo[$row['catid']])) {
$tenseinfo[$row['catid']] = ['catname' =&gt; $row['catname'], 'tenses' =&gt; [$row['name']]];
} else {
$tenseinfo[$row['catid']]['tenses'][] = $row['name'];
}
}
var_dump($tenseinfo);
echo '&lt;table&gt;';
foreach ($tenseinfo as $catid =&gt; $data) {
echo '&lt;tr&gt;&lt;td&gt;' . $catid . '&lt;/td&gt;';
$tensesimpl = implode(', ', $data['tenses']);
echo '&lt;td&gt;' . $tensesimpl . '&lt;/td&gt;';
}
echo '&lt;/table&gt;';

Produces a table like this:
&lt;table&gt;&lt;tr&gt;&lt;td&gt;2&lt;/td&gt;&lt;td&gt;Present Tense, Present Perfect, Imperfect&lt;/td&gt;&lt;tr&gt;&lt;td&gt;17&lt;/td&gt;&lt;td&gt;Preterite, Past Perfect, Subjunctive&lt;/td&gt;&lt;/table&gt;
[upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-08/1578506142-969068-tenses-table.png]
Copy linkTweet thisAlerts:
@jasmine381authorJan 09.2020 — @Sempervivum#1612667 Im using OO not pdo ?can you please convert , i m new so i am also n learning process
Copy linkTweet thisAlerts:
@SempervivumJan 09.2020 — @jasmine381#1612787 Unfortunately I cannot do this right now as I have some other activity today. Shurely someone else can do it for you.

BTW: I took a look at your previous thread and it seems to me that you are going to code some polling or exam? It would be helpful to know the context of your project in order to provide a solution that fits your needs.
Copy linkTweet thisAlerts:
@jasmine381authorJan 09.2020 — @Sempervivum#1612791 Its basically for multi Tenses system with different languages to build a system for translate. I use normal code thats why am asking for normal code
Copy linkTweet thisAlerts:
@SempervivumJan 09.2020 — @jasmine381#1612787 This is the code using mysqli:
$sql = "SELECT t.name, c.catid, c.catname FROM tenses t, tensecategory c WHERE t.catid=c.catid";
$getcat = mysqli_query($conn, $sql);
$tenseinfo = [];
while ($row = mysqli_fetch_assoc($getcat)) {
if (!isset($tenseinfo[$row['catid']])) {
$tenseinfo[$row['catid']] = ['catname' =&gt; $row['catname'], 'tenses' =&gt; [$row['name']]];
} else {
$tenseinfo[$row['catid']]['tenses'][] = $row['name'];
}
}
var_dump($tenseinfo);
echo '&lt;table&gt;';
foreach ($tenseinfo as $catid =&gt; $data) {
echo '&lt;tr&gt;&lt;td&gt;' . $catid . '&lt;/td&gt;';
echo '&lt;td&gt;' . $data['catname'] . '&lt;/td&gt;';
$tensesimpl = implode(', ', $data['tenses']);
echo '&lt;td&gt;' . $tensesimpl . '&lt;/td&gt;&lt;/tr&gt;';
}
echo '&lt;/table&gt;';
Note that this will work only when using the modifyed structure of the tables I posted previously.
Copy linkTweet thisAlerts:
@jasmine381authorJan 10.2020 — @Sempervivum#1612640 Why you use catid on 1st table??
Copy linkTweet thisAlerts:
@jasmine381authorJan 10.2020 — @Sempervivum#1612802 no I modified the table as yours but this message occur ,,,,,,,,,,

"No data value avaible in table "
Copy linkTweet thisAlerts:
@SempervivumJan 10.2020 — Maybe the names I used differ a bit from yours either. Did you adjust them in the PHP? If yes, please post the tables again.
Copy linkTweet thisAlerts:
@jasmine381authorJan 10.2020 — [upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-10/1578639019-672795-tensess.png]


These are my original table and final OUTPUT view where i want to print in this way
Copy linkTweet thisAlerts:
@jasmine381authorJan 10.2020 — &lt;?php<br/>
$sql = "SELECT t.tense_name, c.tenid, c.cat_name FROM tenses t, tensecategory c WHERE t.pktenseid=c.tenid";<br/>
$getcat = mysqli_query($conn, $sql);<br/>
$tenseinfo = [];<br/>
while ($row = mysqli_fetch_assoc($getcat)) {<br/>
if (!isset($tenseinfo[$row['tenid']])) {<br/>
$tenseinfo[$row['tenid']] = ['cat_name' =&gt; $row['cat_name'], 'tenses' =&gt; [$row['tense_name']]];<br/>
} else {<br/>
$tenseinfo[$row['tenid']]['tenses'][] = $row['tense_name'];<br/>
}<br/>
}<br/>
//var_dump($tenseinfo);<br/>
echo '&lt;table&gt;';<br/>
foreach ($tenseinfo as $catid =&gt; $data) {<br/>
echo '&lt;tr&gt;&lt;td&gt;' . $catid . '&lt;/td&gt;';<br/>
echo '&lt;td&gt;' . $data['cat_name'] . '&lt;/td&gt;';<br/>
$tensesimpl = implode(', ', $data['tenses']);<br/>
echo '&lt;td&gt;' . $tensesimpl . '&lt;/td&gt;&lt;/tr&gt;';<br/>
}<br/>
echo '&lt;/table&gt;';<br/>
?&gt;


I modified your code
Copy linkTweet thisAlerts:
@jasmine381authorJan 10.2020 — [upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-10/1578639298-741660-code.png]


And getting this output on this code
Copy linkTweet thisAlerts:
@jasmine381authorJan 10.2020 — @Sempervivum#1612841 Above my latest modifications so is there any way ????
Copy linkTweet thisAlerts:
@SempervivumJan 10.2020 — Of course there is a way. The reason why you don't get the result you need is that you didn't adjust the structure of your tables correctly. They should look like this:

Table tenses

[upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-10/1578676728-809378-tenses-2.png]

Table tensecategory

[upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-10/1578676799-86899-tense-cats.png]

If you have trouble adjusting the tables please explain:

How are these tables filled? Is the content static or is it created and modified by user action and changing permanently?
Copy linkTweet thisAlerts:
@jasmine381authorJan 11.2020 — @Sempervivum#1612873 Can you please explain why you add Catid on tenses table ? infact we are getting tenses id on tenses category Catid column so why we should need catid on tenses table???
Copy linkTweet thisAlerts:
@jasmine381authorJan 11.2020 — User will create tenses or tensescategory as you see on my last screenshot where i combine 2 tables and final print page OUTPUT..
Copy linkTweet thisAlerts:
@SempervivumJan 11.2020 — User will create tenses or tensescategory[/quote]If the user does it there shurely exists a form and a PHP script that reads the GET or POST variables and enters them to the database?
Copy linkTweet thisAlerts:
@andrewjacksonJan 11.2020 — These days, competition is high and you have to learn new skills in web development [url=https://www.jacketscreator.com/product/red-dead-redemption-2-arthur-morgan-leather-jacket/]Arthur Morgan Jacket[/url]

so you can stick to the job and grow in it.
Copy linkTweet thisAlerts:
@jasmine381authorJan 11.2020 — @Sempervivum#1612908 Obviously user post variable to the databases so ,, now i want to print ID's (1, 2, 3) values like (Present , Present Perfect .....) so on as depend on Ids... but still am searching a way to print in this manner
Copy linkTweet thisAlerts:
@miyabhai101Jan 11.2020 — Maybe the names I used differ a bit from yours either. Did you adjust them in the PHP? If yes, please post the tables again.

Copy linkTweet thisAlerts:
@jasmine381authorJan 11.2020 — @miyabhai101#1612953 Yes i post my code
Copy linkTweet thisAlerts:
@jasmine381authorJan 11.2020 — @miyabhai101#1612953 .. please check this is my modified code but still no output
Copy linkTweet thisAlerts:
@jasmine381authorJan 13.2020 — @miyabhai101#1612953 Is there any way to display
Copy linkTweet thisAlerts:
@jasmine381authorJan 13.2020 — @Sempervivum#1612908 any way to solve this issue ???
Copy linkTweet thisAlerts:
@thompsonmaxJan 13.2020 — Hello everyone. Interesting thread. Thanks for the information.[upl-image-preview url=https://www.webdeveloper.com/assets/files/2020-01-13/1578917951-259948-pfe2.pdf]
Copy linkTweet thisAlerts:
@SempervivumJan 13.2020 — @jasmine381#1612904
User will create tenses or tensescategory as you see on my last screenshot where i combine 2 tables and final print page OUTPUT..[/quote]Does this mean that the user enters the string '1,2,4' into some input element? Can you post the relevant part of the form where the user inputs the tenses?
Copy linkTweet thisAlerts:
@jasmine381authorJan 13.2020 — @Sempervivum#1613150 yes user tik the checkbox and tenses coming from other table so here we have IDs of that data ... as u see i posted the screenshots of both tables
Copy linkTweet thisAlerts:
@jasmine381authorJan 14.2020 — Can we use tensecategory query use with while loop then we get cat Id with comma seprated tense Id and use WHERE pktenseid IN() condition ??By this I think we get multiple IDs and by printing we use while loop by creating array and show where we need to print
Copy linkTweet thisAlerts:
@jasmine381authorJan 14.2020 — @Sempervivum#1613150 Can we use tensecategory query use with while loop then we get cat Id with comma seprated tense Id and use WHERE pktenseid IN() condition ??By this I think we get multiple IDs and by printing we use while loop by creating array and show where we need to print ...
Copy linkTweet thisAlerts:
@miyabhai101Mar 30.2020 — @jasmine381#1612955 Thanks for the reply [tubemate](https://get-tubemate.in) [authorityapk.com](https://authorityapk.com)
×

Success!

Help @jasmine381 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 3.28,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 10 SATS,
)...