One place for hosting & domains

      Select

      How To Query the Database in Laravel with Eloquent (Select)



      Part of the Series:
      A Practical Introduction to Laravel Eloquent ORM

      Eloquent is an object relational mapper (ORM) that is included by default within the Laravel framework. In this project-based series, you’ll learn how to make database queries and how to work with relationships in Laravel Eloquent. To practice the examples explained throughout the series, you’ll improve a demo application with new models and relationships.

      If you’ve been following along with all parts of this series so far, you should have the application’s database and commands upgraded to include lists of links.

      The application main route currently shows all links in the database, with no information about lists. In this section, you’ll update the main front end view in order to reflect the new architecture.

      One of the biggest advantages of using an ORM system is the ability to manipulate rows in a database table as objects within your codebase. Eloquent provides several methods that can be accessed directly from models to query the database and filter results without having to write SQL statements. A typical SELECT query to obtain all rows from a table, which in pure SQL looks something like SELECT * FROM links, can be accomplished in Eloquent with code like this:

      $links = Link::all();
      

      The result set is returned as an Eloquent Collection, an iterable object that behaves similarly to an array, but provides extended functionality such as map / reduce methods and the ability to “hydrate” (pull in fresh data) referenced objects only when needed, which helps with overall performance while interacting with the database.

      Updating the Index Route

      If you examine the main application route file, where the index route is defined, you’ll notice that the current application code queries all links using a Link::all() call. Open the routes/web.php file in your code editor:

      routes/web.php
      

      This is how the / route is currently defined in this file:

      routes/web.php

      …
      Route::get('/', function () {
          $links = Link::all()->sortDesc();
          return view('index', [
              'links' => $links
          ]);
      });
      …
      

      As the name suggests, the sortDesc() method is used for ordering results in descending order, from last to first. This is not the same as an ORDER BY clause in an SQL query, since the sortDesc method works at code level, reordering the collection. You can ignore this method for now, as we’ll talk more about ordering results in an upcoming section of this series.

      You’ll now edit this code to obtain a collection of all lists currently registered within the database, so that you can use it later on in the front end view to show all list names.

      First, include a use declaration at the beginning of the file, referencing the LinkList model. This declaration is used to make sure that you don’t need to type the full class name each time you reference that class.

      routes/web.php

      <?php
      
      use IlluminateSupportFacadesRoute;
      use AppModelsLink;
      use AppModelsLinkList;
      ...
      

      Then, change the return of the main route definition to include a lists variable containing all registered lists:

      routes/web.php

      …
          return view('index', [
              'links' => $links,
              'lists' => LinkList::all()
          ]);
      …
      

      This is how the finished file should look. Note the highlighted changes:

      routes/web.php

      <?php
      
      use IlluminateSupportFacadesRoute;
      use AppModelsLink;
      use AppModelsLinkList;
      
      /*
      |--------------------------------------------------------------------------
      | Web Routes
      |--------------------------------------------------------------------------
      |
      | Here is where you can register web routes for your application. These
      | routes are loaded by the RouteServiceProvider within a group which
      | contains the "web" middleware group. Now create something great!
      |
      */
      
      Route::get('/', function () {
          $links = Link::all()->sortDesc();
          return view('index', [
              'links' => $links,
              'lists' => LinkList::all()
          ]);
      });
      
      

      Don’t forget to save the file when you’re done updating it.

      Updating the Index View

      Once you get the main route updated to also provide information about lists, you can edit the referenced view file. Open the resources/views/index.blade.php file in your code editor:

      resources/views/index.blade.php
      

      This file contains the single front end view file that the application uses in its main index page. Within this file, locate the @foreach blade block that loops over the $links variable. It will look like this:

      resources/views/index.blade.php

      ...
                  @foreach ($links as $link)
                      <div class="box link">
                          <h3><a href="https://www.digitalocean.com/community/tutorials/{{ $link->url }}" target="_blank" title="Visit Link: {{ $link->url }}">{{ $link->description }}</a></h3>
                          <p>{{$link->url}}</p>
                      </div>
                  @endforeach
      ...
      

      The code in the loop does not include any information about lists yet. You’ll now include a new line right after the link URL that contains the title of the list where that link is saved. You can use the tag CSS class from Bulma to style this information as a tag:

      resources/views/index.blade.php

      …
      <p>{{$link->url}}</p>
      <p class="mt-2"><a href="#" title="{{ $list->title }}" class="tag is-info">{{ $link->link_list->title }}</a></p>

      Add the highlighted line to your file. The links will be adjusted later on, when you set up individual list pages in an upcoming part of this series.

      Next, locate the paragraph that has the class subtitle, which comes right after the <h1> tag and before the section containing your links. You’ll replace the generic text used in that area with a menu based on your link lists, which you made available previously in a lists variable when you edited the default route in routes/web.php.

      Replace the text in the subtitle section with the highlighted content:

      resources/views/index.blade.php

      
              <p class="subtitle">
                  @foreach ($lists as $list)<a href="#" title="{{ $list->title }}" class="tag is-info is-light">{{ $list->title }}</a> @endforeach
              </p>
      

      The list tags are styled just slightly differently than before, with an is-light additional CSS class to invert the colors of each tag.

      This is how your index.blade.php file should look like after you’re done editing. The highlighted lines indicate the changes:

      resources/views/index.blade.php

      <!DOCTYPE html>
      <html>
      <head>
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1">
          <title>My Awesome Links</title>
          <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bulma@0.9.1/css/bulma.min.css">
      
          <style>
              html {
                  background: url("https://i.imgur.com/BWIdYTM.jpeg") no-repeat center center fixed;
                  -webkit-background-size: cover;
                  -moz-background-size: cover;
                  -o-background-size: cover;
                  background-size: cover;
              }
      
              div.link h3 {
                  font-size: large;
              }
      
              div.link p {
                  font-size: small;
                  color: #718096;
              }
          </style>
      </head>
      <body>
      <section class="section">
          <div class="container">
              <h1 class="title">
                  Check out my awesome links
              </h1>
              <p class="subtitle">
                  @foreach ($lists as $list)<a href="#" title="{{ $list->title }}" class="tag is-info is-light">{{ $list->title }}</a> @endforeach
              </p>
      
              <section class="links">
                  @foreach ($links as $link)
                      <div class="box link">
                          <h3><a href="https://www.digitalocean.com/community/tutorials/{{ $link->url }}" target="_blank" title="Visit Link: {{ $link->url }}">{{ $link->description }}</a></h3>
                          <p>{{$link->url}}</p>
                          <p class="mt-2"><a href="#" title="{{ $list->title }}" class="tag is-info">{{ $link->link_list->title }}</a></p>
                      </div>
                  @endforeach
              </section>
          </div>
      </section>
      </body>
      </html>
      
      

      Save the file when you’re done. The main front end view is now ready to show updated information about link lists.

      If you’d like, you can use the link:new Artisan command now to include new links and test your updated application:

      • docker-compose exec app php artisan link:new
      [secondary_label Output
      Link URL:
      > https://laravel.com/docs/8.x/
      
      Link Description:
      > Laravel Docs
      
      Link List (leave blank to use default):
      > laravel
      
      New Link:
      https://laravel.com/docs/8.x/ - Laravel Docs
      Listed in: laravel
      
      Is this information correct? (yes/no) [no]:
      > yes
      
      Saved.
      

      Then, reload the application page on your browser. If you’re using the included Docker Compose setup, the application should be available on the following local address:

      http://localhost:8000
      

      You’ll obtain a page similar to this:

      Screenshot of Landing Laravel app, updated to support link lists

      In the next part of this series, you’ll set up individual pages for link lists and learn how to make queries to the database using the where() method, for more fine-grained results.

      This tutorial is part of an ongoing weekly series about Laravel Eloquent. You can subscribe to the Laravel tag if you want to be notified when new tutorials are published.



      Source link

      How To Select HTML Elements Using ID, Class, and Attribute Selectors in CSS


      The author selected the Diversity in Tech Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      Writing CSS selectors most often involves setting a condition and locating the element in the HTML that fulfills that condition as true. You can do this broadly with an element selector to select high-level tags, but to gain more control over the selected elements, you can create specific identifiers in the HTML and apply them in the CSS.

      In this tutorial, you will use the id, class, and attribute selectors to scope styles to intentionally written HTML. You will begin by creating an HTML and a CSS file that you will edit throughout the tutorial. You will then add id attributes and use those id values to target styles. You will also add class attributes to elements and use those class values independently and together to create more specific and reusable styles. Lastly, you will use the attribute selector to create styles that match much more specific scenarios than an id or class selector alone can accomplish.

      Prerequisites

      Setting Up the Base HTML and CSS

      To start working with id, class, and attribute selectors, you will first set up the HTML and CSS code that you will work on through the rest of the tutorial. In this section, you will write out all the necessary HTML and some initial CSS styles that will handle layout and start the visual aesthetic.

      To begin, open index.html in your text editor. Then, add the following HTML to the file:

      index.html

      <!doctype html>
      <html>
        <head>
        </head>
        <body>
        </body>
      </html>
      

      Next, go to the <head> tag and add a <meta> tag to define the character set for the HTML file. Then add a <meta> tag defining how mobile devices should render the page, set the title of the page, and finally load the CSS file that you will make later with a <link> tag.

      These additions are highlighted in the following code block. You will encounter this highlighting method throughout the tutorial as code is added and changed:

      index.html

      <!doctype html>
      <html>
        <head>
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1">
          <title>About CSS Selectors</title>
          <link rel="stylesheet" href="https://www.digitalocean.com/community/tutorials/styles.css">
        </head>
        <body>
        </body>
      </html>
      

      After adding the <head> content, move to the <body> element where you will add content for a page talking about CSS selectors. Add the highlighted section from this code block to your index.html file in your text editor:

      index.html

      <!doctype html>
      <html>
        <head>
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1">
          <title>Link and Buttons with State</title>
          <link rel="stylesheet" href="https://www.digitalocean.com/community/tutorials/styles.css">
        </head>
        <body>
          <header>
            <h1>Using CSS Selectors</h1>
          </header>
      
          <main>
            <section>
              <h2>Selecting HTML To Style</h2>
              <p>Selecting the right element and providing the right visual styles is the basis of <a href="https://do.co/community">writing CSS</a> code. Whenever you need to adjust how an element on a webpage looks, using selectors is key.</p>
              <p><a href="https://example.com">Read this to learn more about selectors</a></p>
            </section>
      
            <section>
              <h2>The Type Selector</h2>
              <p>The type selector finds elements on the page by tag name. This is a common way to style elements on the page. Type selectors are often referred to as element selectors.</p>
            </section>
      
            <section>
              <h2>Combining Selectors</h2>
              <p>A combinator selector is defined by a space character between selectors, with the HTML ancestry reading left to right. The right-most selector is the intended target. This can be as complex or as simple as necessary to <em>scope</em>, or provide sufficient specificity, to the intended element.</p>
            </section>
          </main>
        </body>
      </html>
      

      Save your changes to index.html and leave it open in your text editor. Open up your browser and load index.html to render the content of the page with the browser default styles. The following image shows these default styles with black serif text on a white background:

      Black serif text in various sizes with two blue, underlined links.

      Next, return to your text editor and create a file called styles.css. This is the file that you referenced in the <head> element in your index.html. In the styles.css file, add the following code:

      styles.css

      body {
        font-family: sans-serif;
        line-height: 1.5;
        color: #444;
      }
      

      This CSS uses a body type selector to set the default font-family for the page to the browser’s sans-serif font. Then it changes the spacing between lines of text to 1.5 times the font-size. Lastly, the color value changes the text to a dark grey, instead of the default black.

      Save these changes to styles.css and return to the browser to see how these changes will appear, as shown in the following image:

      Dark gray sans-serif text in various sizes with two blue links with underlines.

      You have now set up the starting points for your index.html and your styles.css. In the next section, you will add an id attribute to elements in the HTML and create id selectors in the CSS to apply styles to those elements.

      Using the ID Selector

      In this section, you will learn about the id attribute and its purpose, then add several id attributes to your HTML. Finally, you will use some of these id selectors to apply layout styles.

      The id attribute is a unique identifier that links together content on the page. For example, say there is an element midway down the content of a web page with an id attribute of my-id. By appending that id value with a pound or hash symbol (#) to the URL, such as http://example.com/index.html#my-id, the browser will scroll to that section of the page. Since this can be used as part of the URL and for other scripting purposes, each id value on a page must be unique.

      To begin working with the id attribute, open index.html in your text editor. First, locate the <header> element and give it an id attribute with the value of header. Next, move to the <main> element and give it an id attribute with a value of content. These id attributes provide a unique identifier for the two primary sections of the content. The highlighted additions in the following code block show the changes you will make to your index.html file:

      index.html

      ...
      <header id="header">
        ...
      </header>
      <main id="content">
        ...
      </main>
      ...
      

      Note that well-written HTML will only have one <header> element and one <main> element, so it may seem redundant to assign a unique ID to elements that are already unique. However, applying specific IDs like this can separate your styling from your HTML structure, allowing you to retain consistent styling attached to the IDs even if the underlying HTML changes.

      Be sure to save your changes to index.html before continuing.

      The id selector in CSS uses the same format as when referenced in a URL: a pound sign followed by the name of the id, such as #my-id. To begin using the id selector, open styles.css in your text editor. Then, add the two id attribute values from your index.html as the group combinator #header, #content. You will use this selector to set the content of the <header> and the <main> element uniformly in the middle of the page. The following code block has the selector block and the code necessary for this layout:

      styles.css

      body {
        font-family: sans-serif;
        line-height: 1.5;
        color: #444;
      }
      
      #header,
      #content {
        margin: 1em auto;
        width: 90%;
        max-width: 60ch;
      }
      

      The id selectors will apply a margin of 1em to the top and bottom of both the <header> and the <main> element. The auto value on the margin property keeps the content centered to the page when paired with the width property of 90%. Lastly, the max-width property stops both containers from growing any wider once they reach a size of 60ch, which is equivalent to 60 0 characters in width.

      Save your changes to styles.css then open index.html in your browser. The contents of the page are now centered to the page instead of stretching across the length of the window. The following image illustrates how this appears in the browser:

      Text content centered to roughly half the width of the full size.

      In this section, you learned about the id attribute and used this attribute to apply styles to elements. You also learned that the id attribute is required to have a unique value because of how it can be used by the browser in URLs and in scripting. Next, you will use the class attribute to refactor the code from this section to apply colors to the content with new classes.

      Using the Class Selector

      In this section, you will use the class selector to find specific element on a page using values found in the class HTML attribute. You will refactor the id selectors to use a reusable class instead. The class attribute is meant to create a specific targetable value that styles can be applied to. Unlike the id, the values of class attributes can be reused, which is its advantage over the id selector.

      To begin styling elements with class, start by opening the index.html file in your text editor. On both the <header> and <main> elements, add a class attribute with a value of content-width. The highlighted section of the following code block indicates how to add the class attribute:

      index.html

      ...
      <header id="header" class="content-width">
        ...
      </header>
      <main id="content" class="content-width">
        ...
      </main>
      ...
      

      Save the changes to index.html, then open styles.css in your text editor to create the class selector for this class value.

      The class selector is similar to id in that it has a special character in CSS, which is indicated by the period symbol (.). In the styles.css file, locate the selector with the IDs #header, #content and replace both selectors with the .content-width class selector. The following highlighted code indicates the change you will make in your code:

      styles.css

      body {
        font-family: sans-serif;
        line-height: 1.5;
        color: #444;
      }
      
      .content-width {
        margin: 1em auto;
        width: 90%;
        max-width: 60ch;
      }
      

      Save these change to your styles.css and return to your browser. Refresh index.html and the styles will remain unchanged. The refactor to the code didn’t change the styles, but did change how the elements are selected. The versatility of a class is that the styles can be used more than once and on different element types.

      Next, return to index.html in your text editor and add a class attribute to the <h1> element with a value of style-1. This defines a class name that will be used in your CSS. The following code block indicates this change in the highlighted section:

      index.html

      ...
      <header id="header" class="content-width">
        <h1 class="style-1">Using CSS Selectors</h1>
      </header>
      ...
      

      Next, on each of the <h2> elements within the <main> element, add a class attribute. Give all three of these class attributes the value of style-2. Use the highlighted sections of the following code block for reference:

      index.html

      ...
      <main id="content" class="content-width">
        <section> 
          <h2 class="style-2">Selecting HTML To Style</h2>
          ...
        </section>
      
        <section>
          <h2 class="style-2">The Type Selector</h2>
          ...
        </section>
      
        <section>
          <h2 class="style-2">Combining Selectors</h2>
          ...
        </section>
      </main>
      ...
      

      After adding the class attributes to the elements in the index.html file, be sure to save the changes. Then, open styles.css in your text editor and create a class selector for the style-1 class.

      Add to the end of the styles.css a selector called .style-1. The period tells the browser to look for a class attribute on the page with the name of style-1. Then create a selector block and add a font-size property with a value of 1.25rem, a text-transform property set to uppercase, and a letter-spacing property to put 0.125em space between the characters. The following code block indicates these additions in the highlighted sections:

      styles.css

      ...
      .content-width {
        margin: 1em auto;
        width: 90%;
        max-width: 60ch;
      }
      .style-1 {
        font-size: 1.25rem;
        text-transform: uppercase;
        letter-spacing: 0.125em;
      }
      

      Next, create another class selector, this time for the style-2 class. Be sure to add the period before the name of the class value. Then add to that selector a font-size property with a 2rem value and a font-weight property set to normal. Reference the highlighted code in the following code block:

      styles.css

      ...
      .style-1 {
        font-size: 1.25rem;
        text-transform: uppercase;
        letter-spacing: 0.125em;
      }
      
      .style-2 {
        font-size: 2rem;
        font-weight: normal;
      }
      

      Save your changes to styles.css and refresh the index.html file in your browser. The <h1> content will now be smaller and uppercased while the <h2> content will be a bit larger and no longer bold. The following screenshot showcases how these changes will appear in the browser:

      Text in dark gray with a heading text that is short bold and uppercase, with other heading text much larger and title cased.

      Classes are a powerful selector, as they can be reused and applied to any element on the page. This means you can swap where and how the style-1 and style-2 classes are used, and no changes need to be made to the CSS.

      To make this change, open index.html and change the class attribute value style-1 on the <h1> element to style-2. Afterward, change the style-2 values on the <h2> elements’ class attribute to style-1. Reference the highlighted sections of the following code block for these changes:

      index.html

      ...
      <header id="header" class="content-width">
        <h1 class="style-2">Using CSS Selectors</h1>
      </header>
      
      <main id="content" class="content-width">
        <section> 
          <h2 class="style-1">Selecting HTML To Style</h2>
          ...
        </section>
      
        <section>
          <h2 class="style-1">The Type Selector</h2>
          ...
        </section>
      
        <section>
          <h2 class="style-1">Combining Selectors</h2>
          ...
        </section>
      </main>
      ...
      

      Save these changes to styles.css, return to your browser, and reload index.html. The large, normal font weight of style-2 is now applied to the content in the <h1> element, and the shorter, uppercase styles of style-1 now apply to the section <h2> elements. The following image shows how this will appear in the browser:

      Text in dark gray with a large title case heading text with other heading text that is short bold and uppercase.

      In this section you used class selectors to refactor the id selectors from the previous section. You also created two new class selectors to apply font styles to the <h1> and <h2> heading elements and you swapped those class attribute values to change which elements received what styles. In the next, section you will continue working with class selectors by applying more than one class to an HTML element.

      Combining Class Selectors

      In this section, you will apply multiple class values to the same HTML element to combine the styles from more than one CSS class selector. The advantage of class over id is its reusability, as shown in the previous section. class selectors can also be combined to target more specific elements. The class selector is the most common and preferred CSS selector for this versatility and specificity in finding an element and applying styles.

      To begin, open your styles.css file in your text editor. Then add three new class selectors: .color-1, .color-2, and .color-3. Each of these three selectors will have a color property with similar HSL values that differ by increments of 50 on the hue spectrum. This will provide a color palette that you can apply to elements in the HTML. See the highlighted portions of the following code block for what to add to your CSS file:

      styles.css

      ...
      .style-2 {
        font-size: 2rem;
        font-weight: normal;
      }
      
      .color-1 {
        color: hsl(300, 70%, 40%);
      }
      
      .color-2 {
        color: hsl(250, 70%, 40%);
      }
      
      .color-3 {
        color: hsl(200, 70%, 40%);
      }
      

      Save your changes to styles.css, then open index.html in your text editor. You will now add a color to each of the three <h2> elements in the <main> block. For the first <h2>, add a space after style-1 and then add color-1. The space between each value in the class attribute indicates an additional class name that can be referenced from the CSS. Do the same for the other <h2> elements, except use the color-2 value for the second <h2> and color-3 for the third <h2>.

      The highlighted portions of the following code block show how to format the additional class values:

      index.html

      ...
      <main id="content" class="content-width">
        <section> 
          <h2 class="style-1 color-1">Selecting HTML To Style</h2>
          ...
        </section>
      
        <section>
          <h2 class="style-1 color-2">The Type Selector</h2>
          ...
        </section>
      
        <section>
          <h2 class="style-1 color-3">Combining Selectors</h2>
          ...
        </section>
      </main>
      ...
      

      Save your changes to index.html, return to your browser, and refresh the page. The three <h2> section headings retain the same font styles, but now each have a different color. The following image shows how this will appear in your browser:

      Three short bold uppercase headings with one magenta, one dark blue, and one teal.

      Just as class values can be used together in HTML, the class names can also be combined to create more specific situations that may be present in the HTML. By chaining together class names in the CSS, you can select elements that have both classes at the same time.

      For example, the CSS selector .color-1.color-2 would only select elements that have an HTML class value that contains both color-1 and color-2.

      To try using multiple class names in a selector, open styles.css in your text editor. Create a new selector that combines the color-1 class and the color-2 class. The intent with this combination is to apply a color that is halfway between the defined color value of each of these classes.

      Add the following highlighted sections to your CSS file:

      styles.css

      ...
      .color-3 {
        color: hsl(200, 70%, 40%);
      }
      
      .color-1.color-2 {
        color: hsl(275, 70%, 40%);
      }
      
      .color-2.color-3 {
        color: hsl(225, 70%, 40%);
      }
      

      In this code block, you set the color property of the combined class selector .color1.color-2 to hsl(275, 70%, 40%), since that is halfway between the 300 value of .color-1 and the 250 value of .color-2. Then you did the same to combine .color-2 and .color-3.

      Be sure to save your changes to styles.css, then move over to index.html in your text editor and make the highlighted changes in the following code block:

      index.html

      ...
      <main id="content" class="content-width">
        <section> 
          <h2 class="style-1 color-1 color-2">Selecting HTML To Style</h2>
          ...
        </section>
        ...
        <section>
          <h2 class="style-1 color-3 color-2">Combining Selectors</h2>
          ...
        </section>
      </main>
      ...
      

      Note that the new styling you declared for the combined class selectors will take precedence over the style set for each of the individual classes. This is because the combined class selector is selecting a more specific element than the individual class selectors. For example, the rule you wrote for .color-1 is looking for an HTML element that has color-1 as one of its classes. The .color-1.color-2 rule is looking for an HTML element that has both color-1 and color-2 in the list of its classes. Since the second rule is a subset of the first, it can be said to be more specific, and thus overrides the first.

      With the two combined class CSS rules you have written, there are situations in which both rules could apply. For example, an HTML element with a class value of color-1 color-2 color-3 would satisfy both the .color-1.color-2 and .color-2.color-3 rules. In this case, since they both have the same amount of specificity, the cascade would come into effect, and the last rule declared would override the styling. In this case, .color-2.color-3 would apply its styling, since it is the final rule declared.

      Save the changes to index.html and then refresh the page in your browser. The colors of all three <h2> elements will still be distinct, but will now be visually closer together in hue.

      The following screenshot shows how this will appear in the browser:

      Three short bold uppercase headings with one purple, one dark blue, and one a medium blue.

      This section presented you with a way to string class values together to create more specific scenarios in which to apply styles. In the next section, you will learn how to find an element on the page based on any attribute type.

      Using the Attribute Selector

      In this section, you will use the attribute selector to find and select an element. In the previous sections, you have learned that the id and class selectors have a symbol indicating the kind of attribute to target followed by a value to select. The attribute selector’s format consists of the name of the attribute wrapped in a pair of square brackets, like so: [attr]. The attribute selector can be used with any attribute, including id and class.

      To begin using an attribute selector, open the index.html file in your text editor. You will add the HTML element for abbreviations, <abbr>, as a situation where attribute selectors would be beneficial. Place the <abbr> around the first instance of CSS in the <h1> element. Then, place another <abbr> around the first instance of HTML in the first <h2> element. With the HTML <abbr>, add a title attribute with a value of Hypertext Markup Language. Reference the following code block for how this will be formatted:

      index.html

      ...
      <h1 class="style-2">Using <abbr>CSS</abbr> Selectors</h1>
      ...
      <h2 class="style-1 color-1 color-2">Selecting <abbr title="Hypertext Markup Language">HTML</abbr> To Style</h2>
      ...
      

      Save your changes to index.html, then return to your browser to load the file. If you are using Chrome or Firefox, the browser default for <abbr> elements with a title attribute is to add a dotted underline. The purpose of the visual indicator is to have a user hover over the text, at which point the meaning of the abbreviation, the title attribute’s value, will appear. The following image demonstrates the default <abbr> styling in Firefox:

      Short bold uppercase headings in purple with an abbreviation text with a dotted underline.

      Unlike Chrome and Firefox, some browsers, like Safari and other older browsers, do not have an indicator of when an <abbr> element has a title. This same styling can be brought to these other browsers, along with some customizations with the attribute selector.

      To begin styling with an attribute selector, open the styles.css file in your text editor. Add an attribute selector for How To Select HTML Elements Using ID, Class, and Attribute Selectors in CSS and then curly brackets for the selector block. In the selector block, add the text-decoration property set to underline. Then add the text-decoration-style property with a value of dotted. These two properties will equalize the styling across browsers. Lastly, to add a custom look for the underline, add the property text-decoration-color and set it to a light gray with the value hsl(0, 0%, 70%).

      Reference the following code block for how this will be formatted in your code:

      styles.css

      ...
      .color-2.color-3 {
        color: hsl(225, 70%, 40%);
      }
      
      How To Select HTML Elements Using ID, Class, and Attribute Selectors in CSS {
        text-decoration: underline;
        text-decoration-style: dotted;
        text-decoration-color: hsl(0, 0%, 70%);
      }
      

      Save your changes to styles.css, then return to your browser and refresh index.html. The styling for the HTML text will now have a light gray dotted underline. The following image demonstrates how this will appear in the browser:

      Short bold uppercase headings in purple with an abbreviation text with a light gray dotted underline.

      The one issue with using the How To Select HTML Elements Using ID, Class, and Attribute Selectors in CSS attribute as a selector is that it can be used on almost any element on the page. This means, with the current selector in place, <img> or <a> elements with the title attribute would also receive these styles. In this case, the selector needs to be scoped to work only with the <abbr> element.

      To make this adjustment to the selector, return to styles.css in your text editor. To the left of the How To Select HTML Elements Using ID, Class, and Attribute Selectors in CSS attribute selector, add the abbr element selector. Be sure there is no space between these selectors as this tells the browser to specifically look for an <abbr> element with a title attribute. The following code block demonstrates how this will look, with the added element selector highlighted:

      styles.css

      ...
      abbrHow To Select HTML Elements Using ID, Class, and Attribute Selectors in CSS {
        text-decoration: underline;
        text-decoration-style: dotted;
        text-decoration-color: hsl(0, 0%, 70%);
      }
      

      Save your changes to styles.css. The styles are now scoped to only work with <abbr> elements with a title attribute. The CSS <abbr> in the <h1> element will not receive these styles either. Though it does match the abbr selector, it does not have a title attribute and so does not match.

      In this section, you used the attribute selector to scope styles to an <abbr> element to apply styles when a title attribute is present. In the next section, you will use more advanced capabilities of the attribute selector by matching the value of a given attribute.

      Creating Advanced Attribute Selectors

      The attribute selector is useful for finding particular attributes in the HTML and applying styles to them. However, the attribute selector also has several ways to look for the value of an attribute. In this final section, you will use the attribute selector to look for matching value strings and apply styles that meet these specific conditions.

      To begin, open your styles.css file in the text editor. In the HTML there is a link that goes to the website https://example.com; to target that specific URL link, create an attribute selector for an href attribute. Then, within the brackets following the attribute name, add an equal sign followed by string quotes. Within those quotes, add https://example.com. This selector will target a link that goes to the specified URL. Add a color property with a green color using hsl(150, 70%, 40%).

      The highlighted code in the following code block shows how this is formatted in your styles.css file:

      styles.css

      ...
      abbrHow To Select HTML Elements Using ID, Class, and Attribute Selectors in CSS {
        text-decoration: underline;
        text-decoration-style: dotted;
        text-decoration-color: hsl(0, 0%, 70%);
      }
      
      [href="https://example.com"] {
        color: hsl(150, 70%, 40%);
      }
      

      Save these changes to styles.css and return to your browser to refresh index.html. Only the link that goes to https://example.com has changed to the color green, while the other link on the page remains the default blue. The following image demonstrates how this will appear in the browser:

      Link text in green color with underline.

      Next, return to styles.css in your text editor and add a custom hover state by using the :hover pseudo selector. The selector is set up the same as the previous selector, and immediately follows after the closing square bracket with :hover. Then within the selector add a color property with a darkened version of the previous green by changing the lightness value of the HSL from 40% to 20%.

      Reference the highlighted code in the following code block for how to format this in your code:

      styles.css

      ...
      [href="https://example.com"] {
        color: hsl(150, 70%, 40%); 
      }
      
      [href="https://example.com"]:hover {
        color: hsl(150, 70%, 20%);
      }
      

      Save your changes to styles.css and then reload index.html in your browser. The link that goes to https://example.com now has a hover state that switches to a darker green, as demonstrated in the following animation:

      Animation of green text link with underline changing to dark green when cursor hovers text.

      The attribute selector has additional condition modifiers to look for particular scenarios within a value string. One of these conditions that an attribute can look for is if a value starts with a given string. This is represented with the ^ symbol between the attribute name and the equal sign. This approach can be used to highlight links that are secure, meaning they go to a URL that begins with https:// instead of http://.

      Return to styles.css in your text editor and add an attribute selector that looks for a value that begins with a secure URL by using [href^="https://"]. This selector will only match elements that have an href element that begins with https://. Then, to add a visual indicator that the link is secure, use a pseudo element selector by adding ::before immediately following the closing bracket. Lastly, the ::before pseudo element selector requires a content property to be used, which will add a string before the content of the HTML element. For the value of this content property, use the unicode identifier for the lock emoji, 1F512.

      The highlighted section of the following code block demonstrates this syntax:

      styles.css

      ...
      
      [href^="https://"]::before {
        content: '1F512';
      }
      

      Save your changes to styles.css and return to your web browser to reload index.html. The two links in the content both have secure links and are now indicated as such with the lock emoji, as demonstrated in the following image:

      Links in two different styles with lock emoji preceding the link text.

      In this section you used the advanced features of the attribute selector to look for specific strings in the HTML attribute’s value. There are more options available for attribute value scenarios than were covered here. If you want to learn more about the different attribute selector options, read The Skinny on CSS Attribute Selectors by Chris Coyier.

      Conclusion

      Writing styles that are specific and reusable is a cornerstone to effective CSS architecture. In this tutorial, you learned how and when to use the id selector, how you can use and reuse the class selector in multiple combinations, and how to use the attribute selector and some of its features to create very specific scenarios in which to add styling. These selectors are a powerful asset to create styles that bring life and functionality to a website.

      If you would like to read more CSS tutorials, try out the other tutorials in the How To Style HTML with CSS series.



      Source link

      How To SELECT Rows FROM Tables in SQL


      Introduction

      One of the most fundamental parts of working with databases is the practice of retrieving information about the data held within them. In relational database management systems, any operation used to retrieve information from a table is referred to as a query.

      In this guide, we will discuss the syntax of queries in Structured Query Language (SQL) as well as some of their more commonly used functions and operators.

      Prerequisites

      In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

      Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.

      • You’ll also need a database with some tables loaded with sample data which you can use to practice writing queries. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

      Connecting to MySQL and Setting up a Sample Database

      If your SQL database system runs on a remote server, SSH into your server from your local machine:

      Then open up the database server prompt. If you’re using MySQL, do so by running the following command, making sure to replace sammy with the name of your MySQL user account:

      From the prompt, create a database named queries_db:

      • CREATE DATABASE queries_db;

      If the database was created successfully, you’ll receive output like this:

      Output

      Query OK, 1 row affected (0.01 sec)

      To select the queries_db database, run the following USE statement:

      Output

      Database changed

      After selecting queries_db, create a few tables within it.

      To follow along with the examples used in this guide, imagine that you run a public parks cleanup initiative in New York City. The program is made up of volunteers who commit to cleaning up a city park near their homes by regularly picking up litter. Upon joining the initiative, these volunteers each set a goal of how many trash bags of litter they’d like to pick up each week. You decide to store information about the volunteers’ goals in an SQL database with a table that has five columns:

      • vol_id: each volunteer’s identification number, expressed with the int data type. This column will serve as the table’s primary key, meaning that each value will function as a unique identifier for its respective row. Because every value in a primary key must be unique, this column will also have a UNIQUE constraint applied to it
      • name: each volunteer’s name, expressed using the varchar data type with a maximum of 20 characters
      • park: the name of the park where each volunteer will pick up litter, expressed using the varchar data type with a maximum of 20 characters. Note that multiple volunteers can clean up litter in the same park
      • weekly_goal: each volunteer’s goal for how many bags of litter they’d like to pick up in a week, expressed with the int type
      • max_bags: each volunteer’s personal record for the most bags of litter they picked up in a single week, expressed as an int

      Run the following CREATE TABLE statement to create a table named volunteers that has these five columns:

      • CREATE TABLE volunteers (
      • vol_id int UNIQUE,
      • name varchar(20),
      • park varchar(30),
      • weekly_goal int,
      • max_bags int,
      • PRIMARY KEY (vol_id)
      • );

      Then load the volunteers table with some sample data. Run the following INSERT INTO operation to add seven rows of data representing seven of the program’s volunteers:

      • INSERT INTO volunteers
      • VALUES
      • (1, 'Gladys', 'Prospect Park', 3, 5),
      • (2, 'Catherine', 'Central Park', 2, 2),
      • (3, 'Georgeanna', 'Central Park', 2, 1),
      • (4, 'Wanda', 'Van Cortland Park', 1, 1),
      • (5, 'Ann', 'Prospect Park', 2, 7),
      • (6, 'Juanita', 'Riverside Park', 1, 4),
      • (7, 'Georgia', 'Prospect Park', 1, 3);

      With that, you’re ready to follow the rest of the guide and begin learning how to create queries in SQL.

      Required Query Components: the SELECT and FROM Clauses

      In SQL, a statement is any operation sent to the database system that will perform some sort of task, like creating a table, inserting or deleting data, or changing the structure of a column or table. A query is an SQL statement that retrieves information about data held in a database.

      On its own, a query will not change any existing data held in a table. It will only return the information about the data which the author of the query explicitly requests. The information returned by a given query is referred to as its result set. Result sets typically consist of one or more columns from a specified table, and each column returned in a result set can hold one or more rows of information.

      Here’s the general syntax of an SQL query:

      • SELECT columns_to_return
      • FROM table_to_query;

      SQL statements are made up of various clauses, which consist of certain keywords and the information that these keywords require. At a minimum, SQL queries only require you to include two clauses: the SELECT and FROM clauses.

      Note: In this example syntax, both clauses are written on their own line. However, any SQL statement can alternatively be written on a single line, like this:

      • SELECT columns_to_return FROM table_to_query;

      This guide will follow the common SQL style convention of separating statements onto multiple lines so each line contains only one clause. This aimed to make each example more readable and understandable, but be aware that as long as you don’t include any syntax errors you can write any query on a single line or on as many lines as you’d like.

      Every SQL query begins with a SELECT clause, leading some to refer to queries generally as SELECT statements. After the SELECT keyword comes a list of whatever columns you want returned in the result set. These columns are drawn from the table specified in the FROM clause.

      In SQL queries, the order of execution begins with the FROM clause. This can be confusing since the SELECT clause is written before the FROM clause, but keep in mind that the RDBMS must first know the full working data set to be queried before it starts retrieving information from it. It can be helpful to think of queries as SELECT-ing the specified columns FROM the specified table. Lastly, it’s important to note that every SQL statement must end with a semicolon (;).

      As an example, run the following query. This will retrieve the name column from the volunteers table:

      • SELECT name
      • FROM volunteers;

      Here’s this query’s result set:

      Output

      +------------+ | name | +------------+ | Gladys | | Catherine | | Georgeanna | | Wanda | | Ann | | Juanita | | Georgia | +------------+ 7 rows in set (0.00 sec)

      Even though this operation looked at the entire volunteers table, it only returns the specified column, name.

      You can retrieve information from multiple columns by separating each one’s name with a comma, as in the following query. This will return the vol_id, name, and park columns from the volunteers table:

      • SELECT park, name, vol_id
      • FROM volunteers;

      Output

      +-------------------+------------+--------+ | park | name | vol_id | +-------------------+------------+--------+ | Prospect Park | Gladys | 1 | | Central Park | Catherine | 2 | | Central Park | Georgeanna | 3 | | Van Cortland Park | Wanda | 4 | | Prospect Park | Ann | 5 | | Riverside Park | Juanita | 6 | | Prospect Park | Georgia | 7 | +-------------------+------------+--------+ 7 rows in set (0.00 sec)

      Notice that this result set returns the park column first, followed by the name column and then vol_id. SQL databases will generally return columns in whatever order they’re listed in the SELECT clause.

      There may be times when you want to retrieve every column from a table. Rather than writing out the name of every column in your query, you can instead enter an asterisk (*). In SQL, this is shorthand for “every column.”

      The following query will return every column from the volunteers table:

      • SELECT *
      • FROM volunteers;

      Output

      +--------+------------+-------------------+-------------+----------+ | vol_id | name | park | weekly_goal | max_bags | +--------+------------+-------------------+-------------+----------+ | 1 | Gladys | Prospect Park | 3 | 5 | | 2 | Catherine | Central Park | 2 | 2 | | 3 | Georgeanna | Central Park | 2 | 1 | | 4 | Wanda | Van Cortland Park | 1 | 1 | | 5 | Ann | Prospect Park | 2 | 7 | | 6 | Juanita | Riverside Park | 1 | 4 | | 7 | Georgia | Prospect Park | 1 | 3 | +--------+------------+-------------------+-------------+----------+ 7 rows in set (0.00 sec)

      Notice how this result set’s columns are listed in the same order in which they were defined in the CREATE TABLE statement from the previous Connecting to MySQL and Setting up a Sample Database section. This is how most relational database systems will order columns in the result set when running a query that uses an asterisk in place of individual column names.

      Be aware that you can retrieve information from multiple tables in the same query with the JOIN keyword. We encourage you to follow our guide on How To Use Joins in SQL for details on how to do this.

      Removing Duplicate Values with DISTINCT

      By default, RDBMSs will return every value from a column returned by a query, including duplicate values.

      As an example, run the following query. This will return the values from the volunteers table’s park column:

      • SELECT park
      • FROM volunteers;

      Output

      +-------------------+ | park | +-------------------+ | Prospect Park | | Central Park | | Central Park | | Van Cortland Park | | Prospect Park | | Riverside Park | | Prospect Park | +-------------------+ 7 rows in set (0.00 sec)

      Notice how this result set includes two duplicated values: Prospect Park and Central Park. This makes sense, since multiple volunteers can clean up litter in the same park. There may be times, though, when you only want to know what unique values are held in a column. You can issue queries that remove duplicate values by following SELECT with the DISTINCT keyword.

      The following query will return every unique value in the parks column, removing any duplicates. It’s identical to the previous query except that it includes the DISTINCT keyword:

      • SELECT DISTINCT park
      • FROM volunteers;

      Output

      +-------------------+ | park | +-------------------+ | Prospect Park | | Central Park | | Van Cortland Park | | Riverside Park | +-------------------+ 4 rows in set (0.00 sec)

      This query’s result set has three fewer rows than that of the previous one, since it removed one of the Central Park values and two of the Prospect Park values.

      Note that SQL treats every row of a result set as an individual record, and DISTINCT will only eliminate duplicates if multiple rows share identical values in each column

      To illustrate this, issue the following query that includes the DISTINCT keyword but returns both the name and park columns:

      • SELECT DISTINCT name, park
      • FROM volunteers;

      Output

      +------------+-------------------+ | name | park | +------------+-------------------+ | Gladys | Prospect Park | | Catherine | Central Park | | Georgeanna | Central Park | | Wanda | Van Cortland Park | | Ann | Prospect Park | | Juanita | Riverside Park | | Georgia | Prospect Park | +------------+-------------------+ 7 rows in set (0.00 sec)

      The duplicate values in the park column — three occurrences of Prospect Park and two of Central Park — appear in this result set, even though the query included the DISTINCT keyword. Although individual columns in a result set may contain duplicate values, an entire row must be an exact duplicate of another for it to be removed by DISTINCT. In this case, every value in the name column is unique so DISTINCT doesn’t remove any rows when that column is specified in the SELECT clause.

      Filtering Data with WHERE clauses

      There may be times when you want to retrieve more granular information from tables in your database. You can filter out certain rows by including a WHERE clause in your query after the FROM clause, as in:

      • SELECT columns_to_return
      • FROM table_to_query
      • WHERE search_condition;

      Following the WHERE keyword in this example syntax is a search condition, which is what actually determines which rows get filtered out from the result set. A search condition is a set of one or more predicates, or expressions that can evaluate one or more value expressions. In SQL, a value expression — also sometimes referred to as a scalar expression — is any expression that will return a single value. A value expression can be a literal value (like a string or numeric value), a mathematical expression, or a column name.

      Predicates in a WHERE clause search condition can take many forms, but they typically follow this syntax:

      . . .
      WHERE value expression OPERATOR value_expression
      . . .
      

      After the WHERE keyword, you provide a value expression followed by one of several special SQL operators used to evaluate the column’s values against the value expression (or value expressions) that comes after the operator. There are several such operators available in SQL and this guide will briefly highlight a few of them later in this section, but for illustration purposes it will focus only on one of the most commonly used operators: the equals sign (=). This operator tests whether two value expressions are equivalent.

      Predicates always return a result of either “true,” “false,” or “unknown.” When running SQL queries that contain a WHERE clause, the DBMS will apply the search condition sequentially to every row in the table defined in the FROM clause. It will only return the rows for which every predicate in the search condition evaluates to “true.”

      To illustrate this idea, run the following SELECT statement. This query returns values from the volunteers table’s name column. Instead of evaluating values from one of the table’s columns, however, this WHERE clause tests whether two value expressions — (2 + 2) and 4 — are equivalent:

      • SELECT name
      • FROM volunteers
      • WHERE (2 + 2) = 4;

      Because (2 + 2) is always equal to 4, this search condition evaluates to “true” for every row in the table. Consequently, every row’s name value gets returned in the result set:

      Output

      +------------+ | name | +------------+ | Gladys | | Catherine | | Georgeanna | | Wanda | | Ann | | Juanita | | Georgia | +------------+ 7 rows in set (0.00 sec)

      Because this search condition always returns a result of “true,” it isn’t very useful. You may as well not include a WHERE clause at all, since SELECT name FROM volunteers; will produce the same result set.

      Rather than comparing two literal values like this, you’ll typically use a column name as one of the value expressions in a WHERE clause’s search condition. By doing so, you’re telling the database management system to use each row’s value from that column as a value expression for that row’s iteration of the search condition.

      The following query’s WHERE clause applies a more exclusive search condition to each row. It will return the name and max_bags values from any row whose max_bags value is equal to 4:

      • SELECT name, max_bags
      • FROM volunteers
      • WHERE max_bags = 4;

      Only one volunteer’s max_bags value is exactly equal to 4, so the query only returns that volunteer’s record:

      Output

      +---------+----------+ | name | max_bags | +---------+----------+ | Juanita | 4 | +---------+----------+ 1 row in set (0.00 sec)

      You can also evaluate character string values in search condition predicates. The following query returns the vol_id and name values of every row whose name value is equal to 'Wanda':

      • SELECT vol_id, name
      • FROM volunteers
      • WHERE name="Wanda";

      Because there’s only one volunteer named Wanda, the query only returns the information from that row:

      Output

      +--------+-------+ | vol_id | name | +--------+-------+ | 4 | Wanda | +--------+-------+ 1 row in set (0.00 sec)

      To reiterate, this section’s examples all use the same search condition operator — the equals sign — to filter data. However, there are a number of other types of operators that allow you to write a variety of predicates, offering a high level of control over what information your queries return.

      The SQL standard defines 18 different types of predicates, though not all of them are supported on every RDBMS. Here are five of the most commonly used search condition predicate types and the operators they use:

      Comparison: Comparison predicates compare one value expression with another; in queries, it’s almost always the case that at least one of these value expressions is the name of a column. The six comparison operators are:

      • =: tests whether the two values are equivalent
      • <>: tests whether two values are not equivalent
      • <: tests whether the first value is less than the second
      • >: tests whether the first value is greater than the second
      • <=: tests whether the first value is less than or equal to the second
      • >=: tests whether the first value is greater than or equal to the second

      Null: Predicates that use the IS NULL operator test whether values in a given column are Null
      Range: Range predicates use the BETWEEN operator to test whether one value expression falls between two others
      Membership: This type of predicate uses the IN operator to test whether a value is a member of a given set
      Pattern Match: Pattern matching predicates use the LIKE operator to test whether a value matches a string pattern containing wildcard values

      It’s beyond the scope of this tutorial to go into each of these predicate types in greater detail. If you’d like to learn more about them, though, we encourage you to check out the following guides:

      To learn more about WHERE clauses generally, please see our guide on How To Use WHERE Clauses in SQL.

      Sorting Query Results with ORDER BY

      Sometimes queries will return information in ways that may not be intuitive, or may not suit your particular needs. You can sort query results by appending an ORDER BY clause to the end of your query statement.

      Here’s the general syntax of a query with an ORDER BY clause:

      • SELECT columns_to_return
      • FROM table_to_query
      • ORDER BY column_name;

      To illustrate how this works, say you wanted to know which of your volunteers has the highest max_bags value. You could run the following query which returns the name and max_bags values from the volunteers table:

      • SELECT name, max_bags
      • FROM volunteers;

      However, this query sorts the result set in the order in which each row was added:

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Gladys | 5 | | Catherine | 2 | | Georgeanna | 1 | | Wanda | 1 | | Ann | 7 | | Juanita | 4 | | Georgia | 3 | +------------+----------+ 7 rows in set (0.00 sec)

      For a relatively small data set like this, the order of a result set isn’t that important and you could just scan this result set’s max_bags values to find the highest one. However, this can quickly become tedious when working with larger amounts of data.

      Instead, you could run the same query but add an ORDER BY clause that sorts the result set based each row’s max_bags value:

      • SELECT name, max_bags
      • FROM volunteers
      • ORDER BY max_bags;

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Georgeanna | 1 | | Wanda | 1 | | Catherine | 2 | | Georgia | 3 | | Juanita | 4 | | Gladys | 5 | | Ann | 7 | +------------+----------+ 7 rows in set (0.00 sec)

      As this output indicates, the default behavior of SQL queries that include an ORDER BY clause is to sort the specified column’s values in ascending (increasing) order. You can change this behavior and sort them in descending order by appending the DESC keyword to the ORDER BY clause:

      • SELECT name, max_bags
      • FROM volunteers
      • ORDER BY max_bags DESC;

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Ann | 7 | | Gladys | 5 | | Juanita | 4 | | Georgia | 3 | | Catherine | 2 | | Georgeanna | 1 | | Wanda | 1 | +------------+----------+ 7 rows in set (0.00 sec)

      Conclusion

      By reading this guide, you learned how to write basic queries, as well as filter and sort query result sets. While the commands shown here should work on most relational databases, be aware that every SQL database uses its own unique implementation of the language. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

      If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.



      Source link