Inserting 3 images into database with same foreign key

[glendango]

I have a db table 'images'  that inputs pictures by id ( foreign key)  .

i did have 3 columns in the table  img 1  img 2  img 3...

but i now know this is a sin. 

 

so i now input all the images into one column by id.

id      img...

 

now how  do i display these three images so i can control which picture appears where in the html page..

 

i.e    img 1 = main photo

        img 2 = secondary

        img 3 = third

 

i use a while statement do find the 3 pictures by id...    should i be saving the images with a clear name/file path so i can find them easily with a  while statement. to me seperate cols' with 1 id is far more logical but  want to learn the hard way!

 

thx

Edited September 25, 2017 by glendango

[Psycho]

You can create a column for "order" with a 1, 2, 3, etc. Then sort on the order column when you retrieve the records. If it is possible for users to have "gaps" (e.g. a Main and a Third photo, but no Secondary) then you need to explicit check the "order" value in the returned data and handle accordingly.

 

EDIT: There are many reasons why it is "wrong" to store the images in three columns. Here are a couple

 

1) If you ever need to search/join/etc. on those values you end up having to create overly complicated queries. E.g.

 

SELECT *
FROM user
WHERE image1 LIKE '%search_value%'
   OR image2 LIKE '%search_value%'
   OR image3 LIKE '%search_value%'

Plus, if you DO get a match, you do not know which one matched.

 

Instead, when you have them in a separate table it is much easier

 

SELECT *
FROM users
JOIN images ON users.user_id = images.user_id
WHERE image LIKE '%search_value%'

 

2. Future modifications become much more complicated. If you ever wanted to allow 4, 5 or more images you have to go and modify code in many places and risk creating defects. By using a separate table you can simply have a config value to set the number of allowed images and create your code to work accordingly. Then, if you ever want to change the allowed number of images, you just need to change that config value and there is no need to touch the code.

Edited September 25, 2017 by Psycho

[glendango]

nice one thanks.....in the example code you give:

 

SELECT *
FROM users
JOIN images ON users.user_id = images.user_id
WHERE image LIKE '%search_value%'

 

and in the text you say  create a column for "order" with a 1, 2, 3, etc..   would that order repeat for every image e.g

 

id        img          order

1          jpg           1

1          jpg            2

1          jpg            3

2           jpg            1

2           jpg            2

2           jpg           3 

etc

 

if so , does the order number go into      LIKE '1'     part of the query?  

Edited September 25, 2017 by glendango

[Barand]

"ORDER" is a SQL reserved word. Don't use it for column or table names.

[mac_gyver]

 would that order repeat for every image e.g

 

 

it would repeat, like you have shown, but it would repeat for each id value.

 

if so , does the order number go into      LIKE '1'     part of the query?  

 

 

no. the WHERE ... clause is an example if you were searching for specific image names. if you are retrieving all images belonging to a specific, range, or set of id's, you would not have a WHERE .. clause to match images, you would have a WHERE ... clause to match a specific, range, or set of id's or if retrieving data for all id's, there would be no WHERE ... clause.

[glendango]

'it would repeat, like you have shown, but it would repeat for each id value. '    -     what do you mean?   

 

in that case have you a link so i can see how to do this.....   i just want to store 3 images in a database and then spit them out on a page....   i can only understand example code for some reason explanations seem to make no sense even though i speak english quite well normally. cheers

Edited September 25, 2017 by glendango

[glendango]

'you would have a WHERE ... clause to match a specific, range, or set of id's or if retrieving data for all id's'

 

i said beofre i can while loop everything with a id ,, i need to distinguish each image with the same id...  which i thought your answer about LIKE answereed,,,then you say your example isnt used for that...   

[Psycho]

nice one thanks.....in the example code you give:

 

SELECT *

FROM users

JOIN images ON users.user_id = images.user_id

WHERE image LIKE '%search_value%'

 

and in the text you say  create a column for "order" with a 1, 2, 3, etc..   would that order repeat for every image e.g

 

id        img          order

1          jpg           1

1          jpg            2

1          jpg            3

2           jpg            1

2           jpg            2

2           jpg           3 

etc

 

if so , does the order number go into      LIKE '1'     part of the query?  

 

1) I proposed an order column to address your problem of knowing which of the images is first, second, third.

 

2) I showed some hypothetical examples on why you do not want to store the same type of data in multiple columns. It had nothing to do with addressing your problem, but to help you understand the reasoning of why you would want to store the data in a separate table.

[glendango]

thats great.. 

i ve sorted it by doin git this way. 

 

$res=mysqli_query($conn,"SELECT * FROM portal_images  where id=$id and order = 'second'  ")

 

.....now i ve just got to work out how user can edit/ replace a picture with out deleting it everytime they 'dont' upload an image into the file input.

 

it seems stackoverflow thinks people always want to have a value...i want to ignore a blank value so the database can be updated if user chooses...see you in 2 days... thx again.

Edited September 26, 2017 by glendango

[benanamen]

Before you move on to something else you need to get those variables out of your query and use prepared statements and get rid of the SELECT * and specify the column names you want.

[glendango]

ha,,i will , that is a class logo you have..   red cross , helping dumb shits protect their code...  nice one..  i am learning about all this now...   just for own interest...   do you protect your code against individual hackers or does it protect against mass viruses...    just wondering if the risks are lower if you are a very small company or facebook which is obv a hackers main goal to crack. 

[glendango]

The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help:::

 

trouble is you only know y was your problem after you have fixed x   ..  #life

[gizmola]

ha,,i will , that is a class logo you have..   red cross , helping dumb shits protect their code...  nice one..  i am learning about all this now...   just for own interest...   do you protect your code against individual hackers or does it protect against mass viruses...    just wondering if the risks are lower if you are a very small company or facebook which is obv a hackers main goal to crack.

Small sites get hacked all the time, even though they aren't often the direct targets. Black hats are looking for servers they can use for a variety of things, and they will use automated tools that look for exploited software packages. They will often use google, which is one of the reasons, it is a good idea to turn off or disable a lot of the version identification information in your various server components. Often CMS/Forums/Blogs get exploited, and ultimately the site gets found by a bot, and compromised automagically.

 

The Black hats, will then plant code that turns the server into a drone on their botnet, which they can then use to scan for further servers to compromise or to use in their botnet attacks or spam distribution.

[glendango]

wow scary stuff..      if theyre are loads of people like me coding shit all day then do sites get hacked all the time???   is this what frameworks give you???   a bit of protection? or have many companies been hacked by bad programmers mis selling their skills and not protecting code properly?

Edited September 26, 2017 by glendango

[glendango]

also i ve wondered if you put code on internet like i have asking questions...when using internal server etc for fun..  can bots still make use of  your code and 'plant ' shit  before you go live on web ( i realise this is a mega newbie question but want to cover all options ) 

Edited September 26, 2017 by glendango

[gizmola]

Anything is possible, but again, these guys are in it for money usually, and they aren't going to waste time and money trying to hack your development site.

 

The reason to use Frameworks is to save you time and for you to avoid reinventing the wheel. The better frameworks also are built upon sound software engineering practices, and often have extensive unit test coverage insuring that their component parts work as expected. Even if there are exploits in framework code, in most cases, someone accessing your site won't know that you're even using a specific framework or version of the framework.

[glendango]

thanks for advice. 

[Psycho]

also i ve wondered if you put code on internet like i have asking questions...when using internal server etc for fun..  can bots still make use of  your code and 'plant ' shit  before you go live on web ( i realise this is a mega newbie question but want to cover all options ) 

 

The trick is to write good/secure code from the start. If so, it doesn't matter if every hacker in the world has your code as it would only show them that the code has no attack vectors. However, if there is a flaw in the code, then making the code public would absolutely give an attacker a huge insight into how to compromise your site. The challenge is that if you are not intimately aware of all the possible security vulnerabilities you may not know that one exists. By posting the code to sites like at this, people may be able to point it out to you, but if you don't post it then the vulnerability will likely continue to exist. The fact that you have not made a vulnerability publicly available does not mean an attacker will not find it. They can easily build scripts/processes that look for common vulnerabilities. So, I would lean towards posting the code in hopes of any vulnerabilities being identified and fixed.